Disadvantages of Indexes - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantageâ„¢
  • 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.