The Optimizer aggressively pursues NUSIs when they can cover a query. Covering means that all of the columns requested in a query or data necessary for satisfying the query are also available from an existing index subtable, making it unnecessary to access the base table rows themselves to complete the query. Some vendors refer to this as 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;