WASTED_PERM - Something not understood

Database
Enthusiast

WASTED_PERM - Something not understood

there something which is not clear about wasted perm.

 

If I am creating new table using exiting table like 

 

CREATE TABLE DB_NEW.TBL_NEW
AS DB_EXISTING.TBL_EXISTING
WITH DATA ;

 

And then if I am trying to get space details for both using

 

SEL MAX(CURRENTPERM)*(HASHAMP()+1) AS CURRENTPERM,
SUM(CURRENTPERM) AS ACTUAL_CURRENTPERM,
(MAX(CURRENTPERM)*(HASHAMP()+1))- (SUM(CURRENTPERM)) AS WASTED_PERM,
((MAX(CURRENTPERM)*(HASHAMP()+1))- (SUM(CURRENTPERM))) *100/(MAX(CURRENTPERM)*(HASHAMP()+1)) AS WASTED_PERM_PERCENT,
DATABASENAME , TABLENAME
FROM DBC.TABLESIZEV WHERE DATABASENAME='DB_NEW'
and tablename = 'TBL_NEW'
GROUP BY 5,6
union
SEL MAX(CURRENTPERM)*(HASHAMP()+1) AS CURRENTPERM,
SUM(CURRENTPERM) AS ACTUAL_CURRENTPERM,
(MAX(CURRENTPERM)*(HASHAMP()+1))- (SUM(CURRENTPERM)) AS WASTED_PERM,
((MAX(CURRENTPERM)*(HASHAMP()+1))- (SUM(CURRENTPERM))) *100/(MAX(CURRENTPERM)*(HASHAMP()+1)) AS WASTED_PERM_PERCENT,
DATABASENAME , TABLENAME
FROM DBC.TABLESIZEV WHERE DATABASENAME='DB_EXISTING'
and tablename = 'TBL_EXISTING'
GROUP BY 5,6;

 

Then both the tables are showing big difference in ACTUAL_CURRENTPERM and  wasted perm. 

How can this be possble when both tables are having same data, same structure, same MVC etc. Is defragmentation of sapce happening on block level while createing new copy of table

5 REPLIES 5
Teradata Employee

Re: WASTED_PERM - Something not understood

Hi.

 

First of all, you don't need a UNION if you use the correct WHERE.

 

 WHERE (DATABASENAME='DB_NEW' AND TABLENAME = 'TBL_NEW')

        OR  (DATABASENAME='DB_EXISTING' AND TABLENAME = 'TBL_EXISTING')

 

You don't say HOW MUCH difference in space you get , but let's assume 'BIG'.

 

If the TBL_EXISTING is BLC compressed and you create the TBL_NEW without BLC (DefaultTableMode, SET QUERY_BAND, etc...) you could end up with big differences in space occupation.

 

HTH.

 

Cheers.

 

Carlos.

Enthusiast

Re: WASTED_PERM - Something not understood

BLC is not in picture for any table in this case. Question is on wasted perm not on CURRENTPERM

 

 

Teradata Employee

Re: WASTED_PERM - Something not understood

Hi.

 

>>"Question is on wasted perm not on CURRENTPERM".

 

Then I misunderstood your statement:

 

>>"Then both the tables are showing big difference in ACTUAL_CURRENTPERM and  wasted perm."

 

Which are the sizes and the differences for the TBL_EXISTING and TBL_NEW tables?

 

What happens if you do the same with a new-new table from the new one you created?

 

CREATE TABLE DB_NEW.TBL_NEW2
AS DB_NEW.TBL_NEW
WITH DATA ;

 

Cheers.

 

Carlos.

 

 

Enthusiast

Re: WASTED_PERM - Something not understood

When i tested in-house using create table with data, it always turns to be same size, same amps, same value for (MAX(CURRENTPERM)*(HASHAMP()+1))- (SUM(CURRENTPERM)).
I doubt may be the source table would had multiple update statements or some rows were processed before you compare with new table copy, which would have caused the change of amp numbers to some of the row values, and thus made the difference.
so, I assume the actual source table you have is changing rows/row values continuously, try create table table_new from actual_table and table_new_copy from table_new, then compare table_new and table_new_copy to see if there is any difference.

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

Teradata Employee

Re: WASTED_PERM - Something not understood

It's not entirely clear what you're asking.

Note that (for example) changes to DataBlockSize, MergeBlockRatio, or FreeSpace made after the original table is loaded may not actually have been applied yet.

You can also use PopulateFsysInfoTable macro (or ferret SHOWBLOCKS directly) to better understand how the data is being stored.