Nonunique Secondary Indexes - Teradata Database

Nonunique Secondary Indexes

Nonunique secondary indexes are typically assigned to nonunique column sets that frequently appear in WHERE clause selection conditions, join conditions, ORDER BY and GROUP BY clauses, foreign keys, and miscellaneous other conditions such as UNION, DISTINCT, and any attribute that is frequently sorted.

Note: You can define a NUSI on a row‑level security constraint column.

You can also define a simple NUSI, but not a composite NUSI, on a geospatial column.

Highly selective NUSIs are useful for reducing the cost of frequently made selections and joins on nonunique columns, and provide extremely fast access for equality conditions. This is particularly true for NoPI tables (see “NoPI Tables, Column‑Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes” on page 230), where the only other access method might be a full‑table scan. Note that NUSIs with low selectivity can be less efficient than a full‑table scan.

NUSIs are also useful for range access and in-list conditions and for geospatial indexes.

Also note the following about NUSIs:

NUSI access is always an all-AMPs operation unless the index is defined on the same columns as the primary index. This is allowed when the NUSI is value-ordered or when the table or join index is partitioned and not all the partitioning columns are included in the primary index.

The subtables must be scanned in order to locate the relevant pointers to base table rows. This is a fast lookup process when a NUSI is specified in an equality or range condition because the NUSI rows are either hash‑ordered or value‑ordered on each AMP.

NUSI subtables are not covered by the active read fallback feature (see “Physical Database Integrity” on page 613 for details).