Single-AMP Queries and Partitioned Tables
Primary index 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 267 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 partitioned table and the query
does not provide the partitioning key in a condition, each primary index 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 partition that
contains the sought after value.
Probing always occurs when the partitioning is non‑unique 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 non‑unique unless its partitioning key is also included as part of the primary index definition (see “Row-partitioned Primary Indexes” on page 267). When the partitioning key is included as part of the primary index definition, the system does not need to probe each individual index partition.