Index determination and definition is an important aspect of database design. See Join Index Types and Teradata Index Types
The basic index types are:
- No primary index
- Primary indexes
- 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
Join indexes
Different 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