The statements shown in allow
to use the index
because their WHERE clauses provide start and stop conditions. That is, they
tell the point at which
to begin its scan of the index and where to end the scan.
For example, a statement with a WHERE clause looking for rows for which the
orig_airport value is less than BBB means that
must begin the scan at
the beginning of the index; it can end the scan at BBB. This
means that it avoids scanning the index for most of the entries.
An index scan that uses start or stop conditions is called a matching
index scan.
A WHERE clause can have more than one part. Parts are linked with the
word AND or OR. Each part is called a predicate. WHERE clauses with
predicates joined by OR are not optimizable. WHERE clauses with predicates
joined by AND are optimizable if at least one of the predicates is
optimizable. For example:
SELECT * FROM Flights
WHERE flight_id = 'AA1111' AND
segment_number <> 2
In this example, the first predicate is optimizable; the second predicate
is not. Therefore, the statement is optimizable.
In a few cases, a WHERE clause with predicates joined by OR can be
transformed into an optimizable statement. See
.