PPI Disadvantages

Database
Enthusiast

PPI Disadvantages

Have two doubts on PPI tables :

If partitioning column is not part of the primary index of a PPI table then

1) Why query access slows down if partitioning column is not used in constraint(equality/non-equality).

2) Why primary index have to be defined as non-unique(NUPI).

Thanks
3 REPLIES
Enthusiast

Re: PPI Disadvantages

1) Why query access slows down if partitioning column is not used in constraint(equality/non-equality).

Answer: Because, in the absence of a constraint on the partioning column, all the partitions must be searched.

2) Why primary index have to be defined as non-unique(NUPI).

Answer: Because enforcing a PI uniqueness constraint would require checking for a duplicate key value in each partition, which would be too expensive.

Enthusiast

Re: PPI Disadvantages

Jim,

Thanks for your reasoning but i still have doubt on processing of point 2 :

Defining a UPI with partitioning column being its part,then in that case duplicate check would not be required?

I want to understand how Teradata would proceed in storing the rows in those two cases

1) UPI with partitioning column being part of UPI.
2) Trying to define UPI with partioning column not being of UPI.

Amit
Enthusiast

Re: PPI Disadvantages

The first case (all columns referenced in the partitioning expression are part of UPI) is allowed. Teradata can enforce the uniqueness constraint efficiently. It only needs to check other rows in the same partition having the same row hash.

The other case is not allowed. You will get an error condition on your CREATE TABLE if you attempt it.