What is the difference for below ?

[muthu1802]

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.

[Fred · Accepted 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

[muthu1802]

Thanks Fred.