Join indexes are designed to permit queries (join queries in the case of multitable join indexes) to be resolved by accessing the index instead of accessing, and possibly joining, their underlying base tables.
Multitable join indexes are useful for queries where the index contains all the columns referenced by one or more joins, thereby allowing the index to cover that part of the query, making it possible to retrieve the requested data from the index rather than accessing its underlying base tables. For obvious reasons, an index with this property is often referred to as a covering index (some vendors refer to this as index‑only access). Note that a join index that is defined with an expression in its select list provides less coverage than a join index that is defined using a base column (see “Restrictions on Partial Covering by Join Indexes” on page 575).
Even if a join index does not completely cover a query, the Optimizer can use it to join to its underlying base tables in a way that provides better query optimization than scanning the base tables for all the columns specified in the request (see “Partial Query Coverage” on page 505).
From the point of view of a database designer, the multitable join index permits great adaptability because it provides the flexibility of normalization while at the same time offering the opportunity to create alternative, denormalized virtual data models, providing what might be called materialized views of the database.
Depending on how the index is defined, single-table join indexes can also be useful for queries where the index contains only some of the columns referenced in the statement. This situation is referred to as a partial covering of the query. Multitable join indexes can also be used to partially cover a query for one or more of the tables defined in the join index.
Join indexes are also useful for queries that aggregate columns from tables with large cardinalities. These indexes play the role of prejoin and summary tables without denormalizing the logical design of the database and without incurring the update anomalies and performance problems presented by denormalized tables. While it is true that denormalization often enhances the performance of a particular query or family of queries, it can and often does make other queries perform more poorly.
Unlike traditional indexes, join indexes are not required to store pointers to their associated base table rows. Instead, they are generally used as a fast path final access point that eliminates the need to access and join the base tables they represent. They substitute for rather than point to base table rows. The only exception to this is the case where an index partially covers a query. If the index is defined using either the ROWID keyword, the UPI of its base table, or a USI on the base table as one of its columns, then it can be used to join with the base table to cover the query. A join index defined in this way is sometimes called a global index or global join index. Note that 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.
In this case, you might create a join index to contain only the join column and the ROWID or the UPI column set of the table. The process is as follows.
1 The join index is joined with the other table and any selection conditions that can be evaluated during the join to eliminate disqualified rows are applied.
2 The result of the join is stored in a temporary table and redistributed based on the ROWID, UPI, or USI of the base table to perform a join with the base table.
This join can happen at different points in the query plan, depending on estimated costs. For example, the Optimizer might determine that it is cheaper to perform a join with another table involved in the query before joining to the base table.
Depending on the workloads they are designed to support, you can create join indexes that have either partitioned or nonpartitioned primary indexes or are column‑partitioned. You can also create column‑partitioned join indexes that have no primary index. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics for the usage rules for column‑partitioned join indexes.