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 for a definition of covering), then the Optimizer determines that it would be less costly to access the index rows directly rather than using it to access the base table rows the index 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).
To distribute unique secondary index rows, Teradata Database uses the same hash-based AMP distribution scheme used for the base table data.
Nonunique secondary indexes are stored AMP locally to the rows they index and are not hash-distributed.