15.00 - Nonunique Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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 two 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 264.

  • 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 467.