16.10 - Secondary Index Usage Summary - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Release Date
June 2017
Content Type
User Guide
Publication ID
English (United States)

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).

    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.

USI Summary

  • Can be used to enforce row uniqueness for multiset NUPI and NoPI tables.
  • Guarantee that each complete index value is unique.
  • Any access is, at most, a two-AMP operation.

NUSI Summary

  • Useful for locating rows having a specific value in the index.
  • Can be hash-ordered or value-ordered.

    Value-ordered NUSIs are particularly useful for enhancing the performance of range queries.

  • Any access is an all-AMPs operation with the exception of the case where a NUSI is defined on the same column set as the primary index for the table.
  • If an index is defined with an ORDER BY clause, it counts as 2 consecutive indexes against the table limit of 32 secondary, hash, and join indexes (see Importance of Consecutive Indexes for Value-Ordered NUSIs).

Related Topics

For more information about secondary indexes, see SQL Data Definition Language under the topics CREATE INDEX and CREATE TABLE.