15.00 - Nonunique Secondary Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 Tables, and Column-Partitioned Join Indexes” on page 280), 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 664 for details).