16.10 - Secondary Index Usage Summary - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
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.