Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sun, 02 Jan 2011 @ 12:14:18 GMT

  <Prev Next>   <<First <Prev

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.

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020