15.00 - Purpose - Teradata Database

Teradata Database Design

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

Purpose

Secondary indexes enhance selection by specifying access paths other than the primary index path. Secondary indexes are also used to facilitate aggregate operations.

If a secondary index covers a query (see “Criteria” on page 483 for a definition of covering), then the Optimizer determines that it would be less costly to accesses its rows directly rather than using it to access the base table rows it points to.

Sometimes multiple secondary indexes with low individual selectivity can be overlapped and bit mapped to provide enhanced retrieval when that would decrease the cost of a query (see “NUSI Bit Mapping” on page 479).

Teradata Database uses the standard hashing algorithm (see “Teradata Database Hashing Algorithm” on page 225)) to distribute unique secondary index rows to their subtables.

Nonunique secondary indexes are stored AMP locally to the rows they index and are not hash‑distributed. This is also true for geospatial NUSIs.