Join Index Applications
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, for obvious reasons, is often referred to as a covering index. Some vendors refer to this as 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 Tables, and Column-Partitioned Join Indexes” on page 280). If either an nonpartitioned NoPI table or a column‑partitioned table has no secondary indexes, a covering join index is the only way to access its rows without using a full‑table scan. Be aware that 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 (see “Partial Query Coverage” on page 505). Note that 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. Note that you cannot create an nonpartitioned NoPI join index. PPI join indexes are useful for covering range queries (see “Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value‑Ordered NUSI” on page 506), providing excellent performance by means of row partition elimination (see SQL Request and Transaction Processing).
See “Sparse Join Indexes and Tactical Queries” on page 907 for specific design issues related to join index support for tactical queries.