What is the difference for below ?

General
Enthusiast

What is the difference for below ?

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.

 


Accepted Solutions
Teradata Employee

Re: What is the difference for below ?

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

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: What is the difference for below ?

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

Enthusiast

Re: What is the difference for below ?

Thanks Fred.

Tags (1)
  • Tags: