Useful indexes can use qualifiers
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:
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
entries in the index as it traverses it.
- The following comparisons are valid qualifiers:
- IS NULL
- 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.