Even when there is no definite starting or stopping point for an index
scan, an index can speed up the execution of a query if the index covers the
query. An index covers the query if all the columns
specified in the query are part of the index. These are the columns that are
all columns referenced in the query, not just columns in a WHERE clause. If
so, never has to go to the data pages at all, but can retrieve
all data through index access alone. For example, in the following queries, OrigIndex covers the query:
SELECT orig_airport
FROM Flights
SELECT DISTINCT lower(orig_airport) FROM Flights
FROM Flights
can get all required data out of the index instead of from
the table.
If the query produces an updatable result set,
will retrieve all
data from the data pages even if there is an index that covers the query.