Partial Query Coverage
Partial query coverage allows join indexes whose columns do not match an entire query to be used to cover a subset of it. For example, one or two tables specified by the query might be covered by the join index, but the entire request is not. In some situations, there might be a number of commonly performed queries that join several tables where each of the queries joins two tables, say t1 and t2, on the same columns. For this situation, you can create a join index to join t1 and t2, and the Optimizer can use that join index for any queries that need to perform that join.
Partial query coverage also allows join indexes that contain only a subset of the columns of a base table referenced in the query to cover the query if that join index can be joined to the base table to retrieve additional referenced columns (this form of partial coverage is also used to implement hash indexes: see “Hash Indexes” on page 553).
For example, suppose there is a large table that needs to be joined frequently with another table on a column that is not the distributing column of the table. You can define a join index that redistributes the base table by the join column. However, because of the large number of rows and columns that need to be projected into the join index, the extra disk storage required does not allow the creation of such a join index.
You can also define a join index in such a way that its partial coverage of a query can be extended further by joining with a parent base table to pick up any columns requested by the query but not referenced in the join index definition.
Such a join index, sometimes called a global index or global join index, is defined with one of the following elements, which the Optimizer can use to join it with a parent base table to extend its coverage:
See “Restrictions on Partial Covering by Join Indexes” on page 526 for an example of a global join index.