Join indexes are useful for queries where the index table contains all the columns referenced by one or more joins, thereby allowing the Optimizer to cover all or part of the query by planning to access the index rather than its underlying base tables.
An index that supplies all the columns requested by a query is said to cover that query and is called a covering index. Using a covering index is called index-only access.
A join index can be particularly useful for queries that access both nonpartitioned and column-partitioned tables (see NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes). If either an nonpartitioned NoPI table or a column-partitioned NoPI table has no secondary indexes, a covering join index is the only way to access its rows without using a full-table scan. Join indexes can slow the loading of rows into a table using Teradata Parallel Data Load array INSERT operations.
The Optimizer can also use join indexes that only cover a query partially if the index is defined properly. Query covering is not restricted to join indexes: other indexes can also cover queries either in whole or in part.
Join indexes are also useful for queries that aggregate columns from tables with large cardinalities. For these applications, join indexes play the role of prejoin and summary tables without denormalizing the logical design of the database and without incurring the update anomalies and ad hoc query performance issues frequently presented by denormalized tables.
You can create join indexes that limit the number of rows in the index to only those that are accessed when a frequently run query references a small, well-known subset of the rows of a large base table. You create this type of join index by specifying a constant expression as the RHS of the WHERE clause, which narrowly filters the rows included in the join index. This is known as a sparse join index.
You can also create join indexes that have a partitioned primary index (you can only define a PPI for a join index if the index is not row-compressed) or that are column-partitioned join indexes. You cannot create a nonpartitioned NoPI join index. PPI join indexes are useful for covering range queries, providing excellent performance by means of row partition elimination (see Row Partition Elimination).
See Sparse Join Indexes and Tactical Queries for specific design issues related to join index support for tactical queries.