All Forums General
omarsaeed22 17 posts Joined 11/13
18 Nov 2013
Process of Change of PI in skewd table

Hi Guys,
Can you please let me know the process of changing the Primary Index when a table is skewd?

MOHAMMAD OMAR SAEED
M.Saeed Khurram 544 posts Joined 09/12
18 Nov 2013

Hi Omar,
To change the PI of a table you will need to recreate the table, But before making changes, make sure to analyse the demographics of the candidate PI columns. Once you have choosen a good PI, then you can recreate the table definition with new PI, and reload it, otherwise you can copy data to some temporary table, recreate the table with new PI and restore the data back to it. 
If it is staging table you can create a NOPI table in TD 13.10 or later releases.
 

Khurram

Raja_KT 1246 posts Joined 07/09
19 Nov 2013

You can use hashamp....hashbucket.....hashrow for a field and see how it is distributed per amp and how the skewedness is. 
Once you get the best, then you can proceed with recreating the table with the new PI as per your requirement and convenience.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

omarsaeed22 17 posts Joined 11/13
19 Nov 2013

Thanks Raja and Khurram...

MOHAMMAD OMAR SAEED

omarsaeed22 17 posts Joined 11/13
20 Nov 2013

Hi,
I have few more questions.
1) How to identify the columns to compress and the columns which are not to be compressed?
2) What is the procedure to compress a column?
3) Please provide the script to compress a column and to identify the compression has been done.
 
Please provide the answer to my queries as earlier as possible

MOHAMMAD OMAR SAEED

M.Saeed Khurram 544 posts Joined 09/12
20 Nov 2013

Hi Omar,
You can analyse the distinct values in columns, less the distinct values, more the column is a candidate for compression. 
For example, Gender, country, Codes etc, 
To make a column compressible, you need to add the COMPRESS key word in the column definition.
You can find more on Compression here:
http://www.teradata-sql.com/2012/04/compression-in-teradata.html
 

Khurram

Raja_KT 1246 posts Joined 07/09
20 Nov 2013

Hi Omar,

With TD 14.., there are few enhancements.We can compress Null for data types such as array,period,distinct and structured,  compression for column-partitioned tables,ALC, BLC etc.

 

I think below points may help you:

 

We can verify the table header size by creating a table with compression and without compression. Then we can subtract the currentperm to get the corresponding table header space.A table has upto 1 mb size header.

 

It is not possible to compress PI, Identity, derived table cols, derived period cols, row level constraints cols, std FKs. SIs yes.

 

If you have fields having longer field widths, it is advisable to decrease the number of compressible values.

 

There is also an even  byte alignment scenario where it may spill over to the next presence bits octet in the row header.You need to take care of this too.

 

All in all, you may need to look from many angles before you do compression.

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

gotuchintu 32 posts Joined 12/05
03 Dec 2013

SEL HASHAMP(HASHBUCKET(HASHROW(<PI_Candidate>))) ,COUNT(*) FROM <tablename>
GROUP BY 1

Somnath Roy

You must sign in to leave a comment.