Query to find SKEW FACTOR of a particular table in Teradata

The following query can be used to find the skew factor of a particular table in the Teradata Database.

SELECT DatabaseName , TableName , SUM ( CurrentPerm ) / 1024 ** 2 AS CurrentPerm_MB ,
SUM ( PeakPerm ) / 1024 ** 2 AS PeakPerm_MB , 
( 100 - ( AVG ( CurrentPerm ) / MAXIMUM ( CurrentPerm ) * 100 ) ) AS SkewFactor 
FROM DBC.TableSize 
WHERE databasename = <database_name>
AND TableName = <table_name>
GROUP BY 1 , 2 
ORDER BY SkewFactor DESC ;

Here,

• <database_name> – Name of the Teradata database where the table belongs.
• <table_name> – Name of the Teradata table for which skewfactor needs to be determined.

Alternatively, you can use the below query to find out the most skewed table across the database along with the creation time, lastAltered time, AccessCount, LastAccess time, etc.

SELECT TSIZE.DatabaseName ,
TSIZE.TableName ,
TDEF.CreateTimeStamp AS Created ,
TDEF.LastAlterTimeStamp AS LastAltered ,
TDEF.AccessCount ,
TDEF.LastAccessTimeStamp AS LastAccess ,
SUM(TSIZE.CurrentPerm) AS CurrentPerm ,
SUM(TSIZE.PeakPerm) AS PeakPerm,
(100 - (AVG(TSIZE.CurrentPerm)/MAX(TSIZE.CurrentPerm)*100)) AS SkewFactor 
FROM DBC.TableSize TSIZE JOIN DBC.Tables TDEF 
ON TSIZE.DatabaseName = TDEF.DatabaseName 
AND TABLEKIND='T'
AND TSIZE.TableName = TDEF.TableName GROUP BY 1,2,3,4,5,6;