15.00 - Index Types - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Index Types

Index determination and definition is an important aspect of database design.

Depending on how you choose to count them, Teradata Database supports between 11 and 21 index types.

  • No primary index
  • For this and column-partitioned tables and join indexes, the absence of a primary index is counted as an index type.

  • Unpartitioned NoPI
  • Column‑partitioned
  • Primary indexes
  • Unique unpartitioned
  • Non‑unique unpartitioned primary
  • Single‑level unique partitioned primary
  • Multilevel unique partitioned primary
  • Hash and join indexes
  • The various join indexes are not necessarily mutually exclusive types. Both multitable and single-table simple join indexes can also be sparse, for example. A join index composed of virtual rows, with multiple fixed column sets appended to a single repeating column set is said to be row compressed. Whether compressed or not compressed, a join index can be any of the following types.

  • Row‑compressed
  • Uncompressed
  • Column‑partitioned
  • Partitioned primary index
  • Single‑table simple
  • Single‑table aggregate
  • Single‑table sparse
  • Multitable simple
  • Multitable aggregate
  • Multitable sparse
  • Secondary indexes
  • Unique
  • Non‑unique hash‑ordered on all columns with the ALL option
  • Non‑unique hash‑ordered on a single column with the ALL option
  • Non‑unique value‑ordered on a single column with the ALL option
  • Non‑unique hash‑ordered on all columns without the ALL option
  • Non‑unique hash‑ordered on a single column without the ALL option
  • Non‑unique value‑ordered on a single column without the ALL option
  • The twelve basic types are specified by the first level bullets in the list, while the twenty‑plus count includes the second‑level bullets included for join and non‑unique secondary indexes.

    For additional information about Teradata Database indexes, see Database Design and SQL Fundamentals.