15.00 - Unique Secondary Indexes and Performance - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Unique Secondary Indexes and Performance

USIs provide alternate access paths.

Statistics play an important part in optimizing access when USIs define conditions for the following operations.

  • Joining tables
  • Satisfying WHERE predicates that specify comparisons, string matching, or complex conditionals
  • Satisfying LIKE expressions
  • Processing aggregates
  • Because of the additional overhead for index maintenance, USI values should not change frequently. When you change the value of a secondary index, Teradata Database must undertake the following maintenance operations.

    1 Delete secondary index references to the current value.

    2 Generate secondary index references to the new value.

    The same considerations apply to NUSIs.