 |
 |
Archives of the TeradataForum
Message Posted: Sun, 02 Jan 2011 @ 12:14:18 GMT
| Subj: | | Re: Why PI can not be unique if partition by column is not included in PI |
| |
| From: | | Mittal, Rishikesh |
Why we cannot define UPI on a different column if it is not a PPI column(s).?
To enforce uniqueness on the PI columns for the PPI table, every time a row is inserted or updated on an AMP, every combined partition on that
AMP would need to be probed to see if a row with that PI value already exists. If there are many combined partitions, this may be expensive. That
is why the PI can't be defined to be unique if the partitioning columns are not included in the PI. It's more efficient to check uniqueness (and
it also provides an efficient access path) to define a unique secondary index (USI) on the same columns as the PI in this case.
Combined partition
The term combined partition refers to a set of rows with the same unique combination of values of the partitioning expressions and is
identified by a combined partition number. The combined partition number is computed for a row by an expression, called the combined partitioning
expression, derived from the partitioning expressions.
| |
