General

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-20-2016
06:29 AM

11-20-2016
06:29 AM

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.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-22-2016
03:08 PM

11-22-2016
03:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-22-2016
03:08 PM

11-22-2016
03:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-04-2016
10:39 PM

12-04-2016
10:39 PM

Thanks Fred.