Primary index in Teradata


Primary index in Teradata

I am a beginner in teradata.

I read that PI is used in data distribution in teradata and unline Primary key value in PI column can be udpated.If columns in which PI is defined gets updated  then wont that result in data re-distribution across AMPS, which might make my update query run long?

If PI is defined on multiple columns hash value to decide on AMP number will be applied on both columns on which PI is defined is it?

Teradata Employee

Re: Primary index in Teradata


that's right, updating the PI column(s) for a row means that the row will be re-distributed to another AMP (unless the target AMP is the same).   So, it is good to choose PI column(s) that is not updated frequently.   Some updates are fine, but not many rows very frequently.  

Multiple columns in PI - yes, it means that the combination of columns will be used to decide the AMP number.

You can use the following SQL functions to understand what AMP number is assigned to any value or combination of values:  

select hashamp(hashbucket(hashrow(<column1_value>))) 

select hashamp(hashbucket(hashrow(<column1_value>,<column2_value>)))

Re: Primary index in Teradata

That answers my doubts

Thank you  So much :)