Definition of an Index - Teradata Database

Definition of an Index

Teradata Database uses the term index in two different ways, neither of which is a true index.

Primary and secondary indexes are rowhash values by default, though they can also be locally value‑ordered in some cases. See “Indexing and Hashing” on page 168.

Join and hash indexes are inaccessible tables that can substitute for base tables in various queries, particularly queries that require join processing. In these cases, the index is said to cover the query.

Multitable join indexes are often denormalized prejoins of frequently joined tables that the Optimizer can substitute in a query instead of making the join dynamically.

Single‑table join indexes and hash indexes tend to be used either as virtual vertically‑partitioned tables or, in the case of single‑table join indexes, as a version of a base table that is distributed to the AMPs using a different primary index than its parent table. This is useful for redistributing base table rows in a way that facilitates their being joined to rows from other tables that are distributed on the same primary index values. In this way, a single‑table join index functions as a version of a hashed NUSI.

A join index can be used to substitute for the base table if it has been defined using any of the following components.

The UPI of the underlying base table

The keyword ROWID

You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.

The NUPI of the underlying base table and the keyword ROWID

The NUPI of the underlying base table and the USI of the underlying base table

It is preferable to specify NUPI and ROWID over NUPI and USI.

In the most general definition possible, an index is a column or combination of columns in a table used to access its data in the most high-performing means possible.

The primary index for a table is frequently defined on the same column set that is identified as the primary key during logical database design, but nonunique, non‑primary key columns can also used as the primary index for a table or join index. You can also define tables and some join indexes not to have a primary index (see “NoPI Tables, Column‑Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes” on page 230).