16.10 - Secondary Index Properties - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
  • Can enhance the speed of data retrieval.

    Because of this, secondary indexes are most useful in decision support applications.

  • Do not affect data distribution.
  • Can be a maximum of 32 defined per table.
  • Can be composed of as many as 64 columns.
  • For a value-ordered NUSI, only a single numeric or DATE column of four or fewer bytes may be specified for the sort key.
  • For a hash-ordered covering index, only a single column may be specified for the hash ordering.
  • 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 and deletes.

    Because of this, secondary indexes might not be as useful in OLTP 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 there are 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, and can optionally include covering columns.
  • Cannot be partitioned, but can be defined on a table with a partitioned primary index.