16.20 - Disadvantages of Indexes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update
  • They must be updated every time a row is updated, deleted, or added to a table.

    This is only a consideration for indexes other than the primary index in the Teradata Database environment. The more indexes you have defined for a table, the bigger the potential update downside becomes.

    Because of this, secondary, join, and hash indexes are rarely appropriate for OLTP situations.

  • All Teradata Database secondary indexes are stored in subtables, and join and hash indexes are stored in separate tables, exerting a burden on system storage space.
  • When FALLBACK is defined for a table, a further storage space burden is created because secondary index subtables are always duplicated whenever FALLBACK is defined for a table. An additional burden on system storage space is exerted when FALLBACK is defined for join indexes or hash indexes or both.

For this reason, it is extremely important to use the EXPLAIN modifier to determine optimum data manipulation statement syntax and index usage before putting statements and indexes to work in a production environment.