Predicate Transformations

WHERE clauses with predicates joined by OR are usually 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.

Derby can transform some predicates internally so that at least one of the predicates is optimizable and thus the statement is optimizable. This section describes the predicate transformations that Derby performs to make predicates optimizable.

A predicate that uses the following comparison operators can sometimes be transformed internally into optimizable predicates:

• BETWEEN Transformations
• LIKE Transformations
• Static IN Predicate Transformations
• OR Transformations
• Transitive Closure