Partitioned and Nonpartitioned Primary Indexes - Teradata Vantage

Primary indexes for global temporary, volatile, and standard base tables can be partitioned or nonpartitioned. Nonpartitioned NoPI tables, with the exception of column-partitioned tables, cannot be partitioned because such tables have no primary index to partition. See Column-Partitioned Tables.

An nonpartitioned primary index is the traditional primary index by which rows are assigned to AMPs. Apart from maintaining their storage in row hash order, no additional assignment processing of rows is performed after the rows are hashed to an AMP.

A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions on the AMPs, enabling enhanced performance for range queries that are predicated on primary index values. For details, see Partitioned and Nonpartitioned Primary Indexes and EXPLAIN Request Modifier and Partitioned Primary Index Access.

You can define partitioning on a single level or on multiple levels. A PPI defined on multiple levels is called a multilevel PPI, or MLPPI.

Depending on the number of combined partitions for a table, its partition numbers consume either 2 bytes or 8 bytes in the row header, as the following table specifies.

Single-level column partitioning with no ADD option also consumes 2 bytes in the row header.

See Partitioned and Nonpartitioned Primary Indexes for details on the usage considerations for each of these partitioning strategies.

Use the CASE_N function to define a mapping between conditions to INTEGER numbers.

Use the RANGE_N function to define a mapping of ranges of INTEGER or DATE values to INTEGER numbers.

The partitioning expressions you can define for partitioning a table have restrictions on the data types of their component values, including values returned by functions.

The following table summarizes these restrictions.

The following table explains the abbreviations used in the previous table.