Partial query coverage allows join indexes whose columns do not cover a query to be used to cover a subset of it. For example, one or two tables specified by the query may be covered by the join index, but the entire request is not. There may be commonly performed queries that join tables where each 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.
For example, suppose there is a large table that must 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 so 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, called a global index or global join index, is defined with one of the following elements, which the Optimizer can use to join the index with a parent base table to extend its coverage:
- One of the columns in the index definition is the keyword ROWID. You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See CREATE JOIN INDEX.
- The column set defining the UPI of the underlying base table.
- The underlying base table.
See Restrictions on Partial Covering by Join Indexes for an example of a global join index.