15.00 - Unique Secondary Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Unique Secondary Indexes

USIs are always preferable to NUSIs for row access using a single value, but might not be as efficient as NUSIs for range query access. The usual criterion for choosing between a USI and a NUSI is the data to which it is applied. Data to be indexed tends to be either inherently unique or inherently nonunique.

USIs are useful both for base table access (because USI access is, at worst, a two‑AMP operation) and for enforcing data integrity by applying a uniqueness constraint on a column set. Like a unique primary index, a unique secondary index can be used to guarantee row uniqueness.

USIs can be assigned to any column you want to constrain to contain unique values, including row‑level security constraint columns.