As you learned in the previous section, might be able to use
an index on a column to find data more quickly. If can use an
index for a statement, that statement is said to be optimizable. The statements shown in the preceding section 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 .