Primary index of PPI table has to be NUPI... Why is this so????

Database
Enthusiast

Primary index of PPI table has to be NUPI... Why is this so????

Hi All,

Can u pls explain logic behind this? "Primary index of PPI table has to be NUPI"

I checked other forums but still I am not clear on this. Also I am not able to open this link"http://www.teradata.com/teradataForum/Topic6553-9-1.aspx?Highlight=PPI+UPI"

If anyone have access to it requesting you to copy and paste those contents here.

Thanks,

Tush

3 REPLIES
Enthusiast

Re: Primary index of PPI table has to be NUPI... Why is this so????

Not necessarily, unless you have Partition column outside of UPI. Also for rows in a Table, uniqueness is achieved by combining internal partition number ( 0 for NPPI Table) , row hash and uniqueness value, in that order.

For Partition tables the internal partition number will vary based on partition columns, so there will be issues in achieving uniqueness. there are 2 ways out, Either create a USI on the PI or bring in the Partition column inside the PI and declare it as UPI.

Enthusiast

Re: Primary index of PPI table has to be NUPI... Why is this so????

Got it, Thanks!!!

Enthusiast

Re: Primary index of PPI table has to be NUPI... Why is this so????

The reason why UPI tables have to include the Partition columns as part of the Primary Index is that without this restriction, several PI values could be spread across different partitions.

While this is no problem for a NUPI, it would cause a huge effort in order to check each partition for the creation of duplicates in case of UPDATES or INSERTS into this table.

BR

Roland

Roland Wenzlofsky