Primary index row-partitioning refers to the physical ordering of rows within the table. Partitioned rows are grouped on each AMP based on a partitioning key, which can be one or multiple columns (see Partitioned and Nonpartitioned Primary Indexes). The partitioning columns need not be part of the primary index. However, when the partitioning column is different from the primary index, the performance of tactical queries that pass only a primary index value may be affected.
When the system performs a primary index access to a row-partitioned table and the query does not provide the partitioning key in a condition, each row partition may need to be probed individually to determine if it contains rows reflecting that value. As seen in the following graphic, if you specify a partitioning key value in a WHERE condition (the condition is c_date = '2006-03-15'), the Optimizer can use row partition elimination to probe only the row partition that contains the desired value.
Probing occurs when the primary index is nonunique, because the duplicate primary index rows with different partitioning key values may be spread across the table. The primary index of a partitioned table must be defined as nonunique unless its partitioning key is also included as part of the primary index definition (see Partitioned Primary Indexes). When the partitioning key is included as part of the primary index definition, the system need not probe each individual row partition.