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.
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.
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.