Index Types - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

Index determination and definition is an important aspect of database design. 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