Covering indexes

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, Derby 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

Derby can get all required data out of the index instead of from the table.

Note: If the query produces an updatable result set, Derby will retrieve all data from the data pages even if there is an index that covers the query.