15.00 - Secondary Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Secondary Indexes

While nonunique secondary indexes provide faster set selection (the term set selection refers to the subset of rows returned by a query that does not select all the rows from a table, and is typically used to describe a multirow subset that is returned because of a NUSI condition in a request), unique secondary indexes are useful for retrieving single rows, particularly when a base table either has a nonunique primary index (see “Nonunique Primary Indexes” on page 265) or does not have a primary index, as is the case for NoPI tables, column‑partitioned tables, column‑partitioned join indexes (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280).

Not all queries are based on primary index retrievals. It is common for WHERE clause search criteria to be based on columns other than those making up the primary index for a table, and such occasions often benefit greatly if a secondary index is defined for the column defined for the search criterion.

Similarly, secondary indexes, along with join indexes, are the only way a single row or a row subset can be retrieved from an NoPI table, column‑partitioned table, or column‑partitioned join index without a full‑table or full‑column scan for a column‑partitioned table (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280).

Secondary indexes are frequently selected by the Optimizer when a search condition cannot be satisfied with a primary index retrieval. The Optimizer also selects secondary indexes for query plans when they completely or partially cover a query.

Many retrievals use the primary index, though others might use a secondary index, a hash or join index, a full‑table scan, scan of a subset of row partitions, a full‑column scan of a column‑partitioned table, or a mix of several different index types.

Note that Teradata does not use the term secondary index in the same way it is commonly used to describe a nonclustered index in an indexing system based on B+ trees.