Semantic query transformations

To operate efficiently, SQL Anywhere rewrites your queries into semantically equivalent, but syntactically different, forms. SQL Anywhere performs many different rewrite operations.

If you read the access plans, you frequently find that they do not correspond to a literal interpretation of your original statement. For example, to make your SQL statements more efficient, the optimizer tries as much as possible to rewrite subqueries with joins.

In the Query Rewrite phase, SQL Anywhere performs several transformations in search of more efficient and convenient representations of the query. Because the query may be rewritten into a semantically equivalent query, the plan may look quite different from a literal interpretation of your original query. Common manipulations include:

eliminating of unnecessary DISTINCT conditions
un-nesting subqueries
performing a predicate push-down in UNION or GROUPed views and derived tables
optimizing of OR and IN-list predicates
optimizing of LIKE predicates
converting outer joins to inner joins
eliminating of outer joins and inner joins
discovering exploitable conditions through predicate inference
eliminating of unnecessary case translation
rewriting subqueries as EXISTS predicates

Note

Some query rewrite optimizations cannot be performed on the main query block if the cursor is updatable. Declare the cursor as read-only to take advantage of the optimizations. See Choosing cursor types, and DECLARE CURSOR statement [ESQL] [SP].

For an example of an optimization that cannot be performed if the main query block is an updatable cursor, see Elimination of unnecessary inner and outer joins.

Some of the rewrite optimizations performed during the Query Rewrite phase can be observed in the results returned by the REWRITE function. See REWRITE function [Miscellaneous].

Example

Unlike the SQL language definition, some languages mandate strict behavior for AND and OR operations. Some guarantee that the condition on the left-hand side will be evaluated first. If the truth of the entire condition can then be determined, the compiler guarantees that the condition on the right-hand side will not be evaluated.

This arrangement lets you combine conditions that would otherwise require two nested IF statements into one. For example, in C you can test whether a pointer is NULL before you use it as follows. The nested conditions in the first statement can be replaced using the syntax shown in the second statement below:

if ( X != NULL ) {
   if ( X->var != 0 ) {
      ... statements ...
   }
}

if ( X != NULL && X->var != 0 ) {
      ... statements ...
}

Unlike C, SQL has no such rules concerning execution order. SQL Anywhere is free to rearrange the order of such conditions as it sees fit. The original and reordered forms are semantically equivalent because the SQL language specification makes no distinction between one order or another. In particular, a query optimizer is completely free to reorder predicates in a WHERE, HAVING, or ON clause.

Elimination of unnecessary DISTINCT conditions
Un-nesting subqueries
Predicate push-down in UNION or GROUPed views and derived tables
Optimization of OR and IN-list predicates
Optimization of LIKE predicates
Conversion of outer joins to inner joins
Elimination of unnecessary inner and outer joins
Discovery of exploitable conditions through predicate inference
Elimination of unnecessary case translation
Rewriting subqueries as EXISTS predicates
Inlining user-defined functions
Inlining simple system procedures

Discuss this page in DocCommentXchange.