All Forums Database
tusharzaware1 25 posts Joined 02/13
09 Sep 2015
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

kirthi 65 posts Joined 02/12
09 Sep 2015

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.

tusharzaware1 25 posts Joined 02/13
11 Sep 2015

Got it, Thanks!!!

oshun 43 posts Joined 07/10
27 Sep 2015

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

visit my private blog at http://www.dwhpro.com

You must sign in to leave a comment.