The following queries do
not provide start and
stop conditions for a scan of
OrigIndex, the index
on the
orig_airport column in
Flights. However, some of these queries allow
Derby to do an index
rather than a table scan because the index covers the query.
-- Derby would scan entire table; comparison is not with a
-- constant or with a column in another table
SELECT *
FROM Flights
WHERE orig_airport = dest_airport
-- Derby would scan entire table; <> operator is not optimizable
SELECT *
FROM Flights
WHERE orig_airport <> 'SFO'
-- not valid operator for matching index scan
-- However, Derby would do an index
-- rather than a table scan because
-- index covers query
SELECT orig_airport
FROM Flights
WHERE orig_airport <> 'SFO'
-- use of a function is not simple column reference
-- Derby would scan entire index, but not table
-- (index covers query)
SELECT orig_airport
FROM Flights
WHERE lower(orig_airport) = 'sfo'