I am trying to get the wasted space for each table and each database level due to skewness in teradata . I found below 2 statements in our forum. I am not able to understand the things.
Could some one please help.
Statement 1:
SyntaxEditor Code Snippet
COUNT(*)*(MAX(CurrentPerm)-AVG(CurrentPerm))/(1024**3) AS WastedSpace,
Statement 2:
SyntaxEditor Code Snippet
(MAX(CurrentPerm) * (HASHAMP()+1))/(1024**3) AS wastedStorage
Both are giving different result sets. which one will be the rright choice to get wasted space.
Solved! Go to Solution.
For these queries, both COUNT(*) and (HASHAMP()+1) return the total number of AMPs on your system.
Statement 2 is calculating what you might call the "effective space": (Number of AMPs)*(max space per AMP) - but not all of this space is "wasted".
Statement 1 is a correct way to calculate "wasted space": (Number of AMPs)*(max space per AMP - average space per AMP)
An alternative that gets the same answer would be (Number of AMPs)*(max space per AMP) - sum of space on all AMPs
For these queries, both COUNT(*) and (HASHAMP()+1) return the total number of AMPs on your system.
Statement 2 is calculating what you might call the "effective space": (Number of AMPs)*(max space per AMP) - but not all of this space is "wasted".
Statement 1 is a correct way to calculate "wasted space": (Number of AMPs)*(max space per AMP - average space per AMP)
An alternative that gets the same answer would be (Number of AMPs)*(max space per AMP) - sum of space on all AMPs
Thanks Fred.