16.20 - Index Types - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Index determination and definition is an important aspect of database design. For additional information about Teradata Database indexes, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ SQL Fundamentals, B035-1141

The basic index types are:
  • No primary index
  • Primary indexes
  • Hash and join indexes
  • Secondary indexes

No primary index

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

Primary indexes

Primary indexes include:
  • Unique nonpartitioned
  • Nonunique nonpartitioned 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
  • noncompressed
  • Column-partitioned
  • Partitioned primary index
  • Single-table simple
  • Single-table aggregate
  • Single-table sparse
  • Multitable simple
  • Multitable aggregate
  • Multitable sparse

Secondary indexes

Secondary indexes include:
  • Unique
  • Nonunique hash-ordered on all columns with the ALL option
  • Nonunique hash-ordered on a single column with the ALL option
  • Nonunique value-ordered on a single column with the ALL option
  • Nonunique hash-ordered on all columns without the ALL option
  • Nonunique hash-ordered on a single column without the ALL option
  • Nonunique value-ordered on a single column without the ALL option