Nonunique Indexes - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Nonunique Indexes

A nonunique index does not require its values to be unique. There are occasions when a nonunique index is the best choice as the primary index for a table.

Teradata Database defines three different types of nonunique index.

  • Nonunique primary index (NUPI)
  • NUPIs facilitate joins between major and minor entities by defining them with the same PI (as a UPI in the case of the major entity and as a NUPI in the case of the minor entity) to ensure that the rows to be joined hash to the same AMP.

    How evenly a NUPI distributes the rows for a table across the AMPs depends on the relative singularity of the data comprising the primary index. As a rule of thumb, you should avoid assigning a NUPI to a column set for which many more than several hundred to several thousand rows in the table are likely to have the same NUPI value, depending on the cardinality.

    NUPIs are described in detail in “Unique Primary Indexes” on page 213.

  • Primary AMP index (PA)
  • A PA facilitates joins. Rows are distributed the same as for a NUPI, but on the AMPs they are not stored in hash order. A table or join index with a PA must also be column partitioned. A PA cannot be a unique index.

  • Nonunique secondary index (NUSI)
  • NUSIs can be very useful for providing set selection in many decision support applications.

    NUSIs are described in detail in “Nonunique Secondary Indexes” on page 415.