The Optimizer aggressively pursues NUSIs that can cover a query. Covering means that all columns requested in a query or data necessary for satisfying the query are also available from an existing index subtable, precluding the necessity to access the base table rows to complete the query. Covering is also called index-only access.
Covering of a query can also be partial. In the case, the system can get the row IDs for those base table rows that possibly qualify for a query by preliminary examination from the index but then must also access the base table itself to retrieve the definitively qualified rows.
Example: Index Used to Cover a Query
The following example demonstrates a situation in which the Optimizer can use the index in place of the base table to satisfy the query.
CREATE INDEX idxord (o_orderkey, o_date, o_totalprice) ON orders; SELECT o_date, AVG(o_totalprice) FROM orders WHERE o_orderkey >1000 GROUP BY o_date;