# What's optimizable?

As you learned in the previous section, Derby might be able to use an index on a column to find data more quickly. If Derby can use an index for a statement, that statement is said to be optimizable.

The statements shown in What is an index? allow Derby to use the index because their WHERE clauses provide start and stop conditions. That is, they tell Derby 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 Derby 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.

Note: 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.

Note: In a few cases, a WHERE clause with predicates joined by OR can be transformed into an optimizable statement. See OR transformations.

- Directly optimizable predicates
  Some predicates provide clear-cut starting and stopping points. A predicate provides start or stop conditions, and is therefore optimizable, when at least one of the following conditions is met.
- Indirectly optimizable predicates
  Some predicates are transformed internally into ones that provide starting and stopping points and are therefore optimizable.
- Joins
  Joins specified by the JOIN keyword are optimizable.

Parent topic: Index use and access paths
Related concepts
What is an index?
Covering indexes
Useful indexes can use qualifiers
When a table scan is better
Indexes have a cost for inserts, updates, and deletes