Doubt regarding PPI....

Database

Doubt regarding PPI....

Hi,

I have some doubt in PPI....
If PPI is not a column of the PI, then the PI must be a Non-Unique Primary Index (NUPI).

Can some one explain the logic behind this concept in detail....

4 REPLIES

Re: Doubt regarding PPI....

If PPI is not a column of the PI, then the PI must be a Non-Unique Primary Index (NUPI).

So, what you want the statement above is right, do you want to change it from NUPI to other thing or you want the explaination saying that why the PPI is not a column of the PI.

Regards
Ramakrishna

Re: Doubt regarding PPI....

Hi,

I just want to know why this is so...

Enthusiast

Re: Doubt regarding PPI....

I believe that this was done to keep performance optimal on inserts/updates. If you could create a UPI that did not include the PPI column, then each insert (or update of the primary index columns) would have to go through each partition to make sure that the primary index values for the row were unique. This would be a lot of overhead for each insert and/or update of the PI columns.

You can create a USI on the unique columns if you want to enforce this. Since the USI subtable is arranged in hashing order on each AMP, a duplicate row would be detected most likley in the same data block.

That would be my guess.

Barry

Re: Doubt regarding PPI....

unique primary index will ensure that the values are unique.then why each insert have to make sure that primary index values for the row to be unique..just want to know clearly