15.00 - Disadvantages of Indexes - Teradata Database

Teradata Database Design

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

Disadvantages of Indexes

  • Teradata Database must update index subtables each time an indexed column value in the base table is updated or deleted, or whenever a new row is inserted.
  • This is only a consideration for secondary, join, and hash indexes in the Teradata Database environment. The more secondary, join, and hash indexes you have defined for a table, the larger the potential update maintenance downside becomes.

    Because of this, secondary, join, and hash indexes are generally inappropriate for OLTP situations, though some limited use of secondary and sparse join indexes (when the sparse index is defined on columns that are rarely updated) might be appropriate.

  • All Teradata Database secondary, join, and hash indexes are stored in subtables, so they exert 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 also duplicated whenever fallback is defined for a table.
  • You can optionally specify fallback on join and hash indexes as well. Join and hash indexes do not default to fallback if their underlying base tables are defined with fallback.

    For these reasons, it is extremely important to use EXPLAIN request modifiers to determine optimum data manipulation statement syntax and index usage before putting those statements (and indexes) to work in a production environment.