Definition of an Index
Teradata Database uses the term index in two different ways, neither of which is a true index.
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.
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.
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).