Find Skew Factor in Tables in Teradata

When Teradata tables are not distributed properly this can cause significant performance problems. Storage space is also not utilized properly. Therefore, identifying and monitoring tables that are not distributed properly across AMPs is very important when working with Teradata databases. The following query will reveal the skew factor of tables in Teradata

Check skew factor of all table in Database

SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))

AS SkewFactor FROM DBC.TableSize

WHERE databasename ='<DATABASE_NAME>'

GROUP BY 1,2

ORDER BY SkewFactor DESC;

Check skew factor of some specific tables in Databse

SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))

AS SkewFactor FROM DBC.TableSize

WHERE databasename ='<DATABASE_NAME>'

AND TableName IN ('<TABLE_NAME>','<TABLE_NAME>')

GROUP BY 1,2

ORDER BY SkewFactor DESC;

Example:

Check skew factor of all table in Database

SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))

AS SkewFactor FROM DBC.TableSize

WHERE databasename ='DBTgtT'

GROUP BY 1,2

ORDER BY SkewFactor DESC;

Check skew factor of some specific tables in Databse

SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))

AS SkewFactor FROM DBC.TableSize

WHERE databasename ='DBTgtT'

AND TableName IN ('SALES' , 'CUSTOMERS')

GROUP BY 1,2

ORDER BY SkewFactor DESC;