Internal Language Transformations
Page 2 of 8

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.

Cloudscape 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 Cloudscape performs to make predicates optimizable.

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

BETWEEN Transformations

A BETWEEN predicate is transformed into equivalent predicates that use the >= and <= operators, which are optimizable. For example:

booking_date BETWEEN DATE'1998-12-01' AND DATE'1998-12-15'

is transformed into

booking_date >= DATE'1998-12-01'
AND booking_date <= '1998-12-15'

LIKE Transformations

Character String Beginning with Constant

A LIKE predicate in which a column is compared to a character string that begins with a character constant (not a wildcard) is transformed into three predicates: one predicate that uses the LIKE operator, one that uses the >= operator, and one that uses the < operator. For example:

country LIKE 'Ch%i%'

becomes

country LIKE 'Ch%i%'
AND country >= 'Ch'
AND country < 'Ci'

The first (LIKE) predicate is not optimizable, but the new predicates added by the transformation are.

When the character string begins with one more character constants and ends with a single "%", the first LIKE clause is eliminated. For example:

country LIKE 'Ch%'

becomes

country >= 'Ch'
AND country < 'Ci'

Character String Without Wildcards

A LIKE predicate is transformed into a predicate that uses the = operator (and a NOT LIKE predicate is transformed into one that uses <>) when the character string does not contain any wildcards. For example:

country LIKE 'Chile'

becomes

country = 'Chile'

and

country NOT LIKE 'Chile'

becomes

country <> 'Chile'

Predicates that use the = operator are optimizable. Predicates that use the <> operator are sargable.

Unknown Parameter

'The situation is similar to those described above when a column is compared using the LIKE operator to a parameter whose value is unknown in advance (dynamic parameter, join column, etc.).

In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate, one predicate using the >= operator, and one predicate using the < operator. For example:

country LIKE ?

is transformed into

country LIKE ?
AND country >= InternallyGeneratedParameter
AND country < InternallyGeneratedParameter

where the InternallyGeneratedParameters are calculated at the beginning of execution based on the value of the parameter.

NOTE: This transformation can lead to a bad plan if the user passes in a string that begins with a wildcard or a nonselective string as the parameter. Users can work around this possibility by writing the query like this (which is not optimizable):

(country || '') LIKE ?

Static IN Predicate Transformations

A static IN list predicate is one in which the IN list is composed entirely of constants. Cloudscape calculates the minimum and maximum values in the list and transforms the predicate into three new predicates: the original IN predicate, one that uses the >= operator, and one that uses the <= operator. The second and third are optimizable. For example:

orig_airport IN ('ABQ', 'AKL', 'DSM')

is transformed into

orig_airport IN ('ABQ', 'AKL', 'DSM')
AND orig_airport >= 'ABQ'
AND orig_airport <= 'DSM'

NOT IN Predicate Transformations

NOT IN lists are transformed into multiple predicates that use the <> operator. <> predicates are not optimizable, but they are sargable. For example:

orig_airport NOT IN ('ABQ', 'AKL', 'DSM')

becomes

orig_airport <> 'ABQ'
AND orig_airport <> 'AKL'
AND orig_airport <> 'DSM'

In addition, large lists are sorted in ascending order for performance reasons.

OR Transformations

If all the OR predicates in a WHERE clause are of the form

simple column reference = Expression

where the columnReference is the same for all predicates in the OR chain, Cloudscape transforms the OR chain into an IN list of the following form:

simple column reference IN (Expression1, Expression2, ..., ExpressionN)

The new predicate may be optimizable.

For example, Cloudscape can transform the following statement:

SELECT * FROM Flights
WHERE flight_id = 'AA1111'
OR flight_id = 'US5555'
OR flight_id = ?

into this one:

SELECT * FROM Flights
WHERE flight_id IN ('AA1111', 'US5555', ?)

If this transformed IN list is a static IN list, Cloudscape also performs the static IN list transformation (see Static IN Predicate Transformations).