The decision to define a single-level or multilevel Partitioned Primary Index (PPI) for a table depends on how its rows are most frequently accessed. PPIs are designed to optimize range queries while also providing efficient primary index join strategies and may be appropriate for other classes of queries. Performance of such queries is improved by accessing only the rows of the qualified partitions.
A PPI increases query efficiency by avoiding full table scans without the overhead and maintenance costs of secondary indexes.
The most important factors for PPIs are accessibility and maximization of partition elimination. In all cases, it is critical for parallel efficiency to define a primary index that distributes the rows of the table fairly evenly across the AMPs.