15.00 - Purposes of the Primary Index - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Purposes of the Primary Index

  • To define the distribution of the rows to the AMPs.
  • With the exception of NoPI tables and column‑partitioned tables and join indexes, Teradata Database distributes table rows across the AMPs on the hash of their primary index value (see “Teradata Database Hashing Algorithm” on page 225). The determination of which hash bucket, and hence which AMP the row is to be stored on, is made solely on the value of the primary index.

    The choice of columns for the primary index affects how even this distribution is. An even distribution of rows to the AMPs is usually of critical importance in picking a primary index column set.

  • To provide access to rows more efficiently than with a full‑table scan.
  • If the values for all the primary index columns are specified in a DML statement, single‑AMP access can be made to the rows using that primary index value.

    With a row partitioned primary index, faster access is also possible when all the values of the partitioning columns are specified or if there is a constraint on partitioning columns.

    Other retrievals might use a secondary index, a hash or join index, a full‑table scan, or a mix of several different index types.

  • To provide for efficient joins.
  • If there is an equijoin constraint on the primary index of a table, it may be possible to do a direct join to the table (that is, rows of the table might not have to be redistributed, spooled, and sorted prior to the join).

  • To provide a means for efficient aggregations.
  • If the GROUP BY key is on the primary index of a table, it is often possible to perform a more efficient aggregation.