Matching index scans can use qualifiers that further restrict the result
set. Remember that a WHERE clause that contains at least one optimizable predicate
is optimizable. Nonoptimizable predicates can be useful in other ways.
Consider the following query:
SELECT *
FROM FLIGHTS
WHERE orig_airport < 'BBB'
AND orig_airport <> 'AKL'
The second predicate is not optimizable, but the first predicate is. The
second predicate becomes a qualification for which
Derby evaluates the
entries in the index as it traverses it.
- The following comparisons are valid qualifiers:
- =
- <
- <=
- >
- >=
- IS NULL
- BETWEEN
- LIKE
- <>
- IS NOT NULL
- The qualifier's reference to the column does not have to be a simple
column reference; you can put the column in an expression.
- The qualifier's column does not have to be the first column in the
index and does not have to be contiguous with the first column in the index.