Almost all statements pass through all query processing phases. However, there are two main exceptions: queries that benefit from plan caching (queries whose plans are already cached by the database server), and bypass queries.
Plan caching For queries contained inside stored procedures and user-defined functions, the database server may cache the execution plans so that they can be reused. For this class of queries, the query execution plan is cached after execution. The next time the query is executed, the plan is retrieved and all the phases up to the execution phase are skipped. See Plan caching.
Bypass queries Bypass queries are a subclass of simple queries that have certain characteristics that the database server recognizes as making them eligible for bypassing the optimizer. Bypassing optimization can reduce the time needed to build an execution plan.
If a query is recognized as a bypass query, a heuristic rather than cost-based optimization is used—that is, the semantic transformation and optimization phases may be skipped and the query execution plan is built directly from the parse tree representation of the query.
A simple query is a SELECT, INSERT, DELETE, or UPDATE statement with a single query block and the following characteristics:
The query block does not contain subqueries or additional query blocks such as UNION, EXCEPT, and common table expressions.
The query block references a single base table or materialized view.
The query block may include the TOP N, FIRST, ORDER BY, or DISTINCT clauses.
The query block may include aggregate functions without GROUP BY or HAVING clauses.
The query block does not include window functions.
The query block expressions do not include NUMBER, IDENTITY, or subqueries.
The constraints defined on the base table are simple expressions.
A complex statement may be transformed into a simple statement after the semantic transformation phase. When this occurs, the query can be processed by the optimizer bypass or have its plan cached by the SQL Anywhere server.
You can force queries that qualify for plan caching, or for bypassing the optimizer, to be processed by the SQL Anywhere optimizer. To do so, use the FORCE OPTIMIZATION clause with any SQL statement.
You can also try to force a statement to bypass the optimizer. To do so, use the FORCE NO OPTIMIZATION clause of the statement. If the statement is too complex to bypass the optimizer - possibly due to database option settings or characteristics of the schema or query - the query fails and an error is returned.
The FORCE OPTIMIZATION and FORCE NO OPTIMIZATION clauses are permitted in the OPTION clause of the following statements:
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |