Process of Change of PI in skewd table

General
Enthusiast

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?

7 REPLIES
Enthusiast

Re: Process of Change of PI in skewd table

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
Enthusiast

Re: Process of Change of PI in skewd table

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

Enthusiast

Re: Process of Change of PI in skewd table

Thanks Raja and Khurram...

Enthusiast

Re: Process of Change of PI in skewd table

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

Enthusiast

Re: Process of Change of PI in skewd table

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
Enthusiast

Re: Process of Change of PI in skewd table

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

Enthusiast

Re: Process of Change of PI in skewd table

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

GROUP BY 1