Primary indexes can be row partitioned or nonpartitioned. A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions on the AMPs. A PPI is defined to be either single-level or multilevel. Multilevel partitioning allows each partition to be subpartitioned.
The greatest potential gain in row partitioning a primary index is the ability to access only the rows of the qualified partitions while skipping partitions that do not contain rows meeting the search conditions of a query.
A PPI increases query efficiency by avoiding full-table scans without the overhead and maintenance costs of secondary indexes. A multilevel PPI provides multiple access paths to the rows in the base table and can improve query performance through row partition elimination at each of the various levels or combination of levels.
PPIs are designed to optimize range queries while also providing efficient PI join strategies. A range query requests data that falls within specified boundaries. A common example of this is queries that involve date ranges.
Note: Column partitioning is not supported for tables with a primary index.
Recommendation: Consider defining a table with a PPI to support either of the following workload characteristics: