Secondary Index Usage Summary - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Secondary Index Usage Summary

All Teradata Database secondary indexes have the following properties:

  • Can enhance the speed of data retrieval.
  • Because of this, secondary indexes are most useful in decision support applications.

  • Do not affect base table data distribution.
  • Maximum of 32 secondary, hash, and join indexes defined per table. Each composite NUSI that specifies an ORDER BY clause counts as 2 consecutive indexes in this calculation (see “Importance of Consecutive Indexes for Value‑Ordered NUSIs” on page 434).
  • The limit of 32 indexes applies to any combination of secondary, hash, and join indexes defined on a table, ranging from 0 secondary indexes and 32 join indexes, 11 hash indexes, 11 join indexes, and 10 secondary indexes to 32 secondary indexes and 0 join indexes.

    This includes the system‑defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

  • Can be composed of as many as 64 concatenated columns.
  • Can include columns defined with a UDT data type.
  • Cannot contain columns defined with XML, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT, XML‑based UDT, Period, or JSON data types.
  • You can define a NUSI on a single column with a geospatial data type, but you cannot define a USI on a geospatial column.

    You cannot define a composite NUSI that contains a geospatial column.

  • Cannot be defined on global temporary trace tables.
  • Can be created or dropped dynamically as data usage changes or if they are found not to be useful for optimizing data retrieval performance.
  • Require additional disk space to store subtables.
  • Require additional I/Os on INSERTs, DELETEs, and possibly on UPDATEs and MERGEs.
  • Because of this, secondary indexes are not nearly as useful in OLTP applications as they are in DSS applications.

  • Should not be defined on columns whose values change frequently.
  • Should not include columns that do not enhance selectivity.
  • Should not use composite secondary indexes when multiple single column indexes and bit mapping might be used instead.
  • Composite secondary index is useful if it reduces the number of rows that must be accessed.
  • The Optimizer does not use composite secondary indexes unless a WHERE clause condition specifies explicit values for each column in the index.
  • Most efficient for selecting a small number of rows.
  • Can be unique or nonunique.
  • NUSIs can be hash-ordered or value-ordered.
  • Ordering for NUSIs defined with an ORDER BY clause is restricted to a single numeric or DATE column of 4 of fewer bytes.
  • If they cover, or partially cover, a query, then they further improve their usefulness.