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