Optimizations performed during query processing

In the Query Rewrite phase, SQL Anywhere performs semantic 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 your original query. Common manipulations include:

  • eliminating 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 outer joins and inner joins

  • discovering exploitable conditions through predicate inference

  • eliminating unnecessary case translation

  • rewriting subqueries as EXISTS predicates

  • inferring sargable IN predicates, which can be used for partial index scans from OR predicates that cannot be transformed into AND 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.

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

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1