Single-AMP Queries and Partitioned Tables
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 “Row-partitioned Primary Indexes” on page 215 for details). 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 might 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 might
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 (in this case, the condition is c_date = ‘2006-03-15’), the Optimizer can use row partition elimination to probe only the row partition
that contains the sought after value.
Probing always occurs when the primary index is nonunique because of the possibility that duplicate primary index rows with different partitioning key values might 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 “Row-partitioned Primary Indexes” on page 215). When the partitioning key is included as part of the primary index definition, the system does not need to probe each individual row partition.