![]() |
Internal Language Transformations
|
Reference Manual |
Predicate TransformationsWHERE 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 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 TransformationsA 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'
booking_date >= DATE'1998-12-01' LIKE TransformationsCharacter String Beginning with ConstantA 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%' 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 >= 'Ch' Character String Without WildcardsA 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: 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 ? 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 TransformationsA 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')
orig_airport IN ('ABQ', 'AKL', 'DSM') NOT IN Predicate TransformationsNOT 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')
orig_airport <> 'ABQ' In addition, large lists are sorted in ascending order for performance reasons. OR TransformationsIf 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
SELECT * FROM Flights If this transformed IN list is a static IN list, Cloudscape also performs the static IN list transformation (see Static IN Predicate Transformations). |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |