NUSIs and Query Covering
The Optimizer aggressively pursues NUSIs when they can cover a query. The expression 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.
For column-partitioned tables, other access methods are available, and may be chosen by the Optimizer based on cost comparisons.
Covering of a query can also be partial, or an index can fail to cover any aspect of a query.
The Optimizer also selects a secondary index to process an aggregation on indexed values if the index covers the necessary values.
In the case of a partial covering index for single‑table access, 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. Even a partial covering index can accelerate the processing of a query, especially when the base table being accessed is very large and the query constraints on the partially covering NUSI are highly selective. The optimizer determines if a partial covering index should be used based on the cost comparisons.