Query to find Actual space, Effective Space, Wasted Space and skew factor in Database Tables in Teradata

Space in GB:

SELECT DatabaseName, TableName                 

,CAST(SUM(CurrentPerm) /1024**3 as DECIMAL (6,0)) AS ActualSpaceGB

,CAST(MAX(CurrentPerm)*(HASHAMP()+1) /1024**3 as DECIMAL (6,0)) as EffectiveSpaceGB

,EffectiveSpaceGB-ActualSpaceGB AS WasteSpaceGB

,CAST(100*(((MAX(CurrentPerm)*(HASHAMP()+1) /1024**3)-(SUM(CurrentPerm) /1024**3))/ (MAX(CurrentPerm)*(HASHAMP()+1) /1024**3)) as DECIMAL (6,0)) AS "WasteSpace%"

,CAST((100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) as DECIMAL (6,0)) AS SkewFactor

FROM DBC.TableSize

WHERE DatabaseName='<Database Name>'

GROUP BY 1,2

ORDER BY 5 DESC;

 

Space in MB:

SELECT DatabaseName, TableName                 

,CAST(SUM(CurrentPerm) /1024**2 as DECIMAL (6,0)) AS ActualSpaceMB

,CAST(MAX(CurrentPerm)*(HASHAMP()+1) /1024**2 as DECIMAL (6,0)) as EffectiveSpaceMB

,EffectiveSpaceMB-ActualSpaceMB AS WasteSpaceMB

,CAST(100*(((MAX(CurrentPerm)*(HASHAMP()+1) /1024**2)-(SUM(CurrentPerm) /1024**2))/ (MAX(CurrentPerm)*(HASHAMP()+1) /1024**2)) as DECIMAL (6,0)) AS "WasteSpace%"

,CAST((100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) as DECIMAL (6,0)) AS SkewFactor

FROM DBC.TableSize

WHERE DatabaseName='Expt'

GROUP BY 1,2

ORDER BY 5 DESC;

 

Comments