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 432 for a definition of covering), then the Optimizer determines that it would be less costly to access 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 428).
Teradata Database uses the standard hashing algorithm (see “Teradata Database Hashing Algorithm” on page 173)) 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.