16.10 - Partitioned and Nonpartitioned Primary Indexes - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Release Number
June 2017
English (United States)
Last Update

Primary indexes can be partitioned or nonpartitioned.

A nonpartitioned primary index (NPPI) is the traditional primary index by which rows are assigned to AMPs.

A partitioned primary index (PPI) allows rows to be partitioned, based on some set of columns, on the AMP to which they are distributed, and ordered by the hash of the primary index columns within the partition.

A PPI can improve query performance through partition elimination. A PPI provides a useful alternative to an NPPI for executing range queries against a table, while still providing efficient access, join, and aggregation strategies on the primary index.

A multilevel PPI allows each partition at a level to be subpartitioned based on a partitioning expression, where the maximum number of levels is 15.

A multilevel PPI provides multiple access paths to the rows in the base table and can improve query performance through partition elimination at each of the various levels or combination of levels.

A PPI can only be defined as unique if all the partitioning columns are included in the set of primary index columns.