Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Query Optimization and Execution

Query Optimization and Execution Next Page

Phases of query processing

Optimization is essential in generating a suitable access plan for a query. Once each query is parsed, the optimizer analyzes it and decides on an access plan that computes the result using as few resources as possible. Optimization begins just before execution. If you are using cursors in your application, optimization commences when the cursor is opened. Unlike many other commercial database systems, SQL Anywhere usually optimizes each statement just before executing it. Because SQL Anywhere performs just-in-time optimization of each statement, the optimizer has access to the values of host and stored procedure variables, which allows for better selectivity estimation analysis. In addition, just-in-time optimization allows the optimizer to adjust its choices based on the statistics saved after previous query executions.

The following list describes the phases that a statement goes through starting with the annotation phase and ending with its execution. It also describes the assumptions that underlie the design of the optimizer, and discusses selectivity estimation, cost estimation, and the other steps of optimization.

Statements that have no result sets, such as UPDATE or DELETE statements, also have a query execution plan.

Queries that bypass optimization

Almost all statements pass through the phases described above, with two exceptions: simple queries that bypass the optimizer, and queries whose plans are already cached by the database server. See Queries that bypass optimization.

If a query is recognized as a simple query, a heuristic rather than cost-based optimization is used—that is, the optimization phase is skipped and the query execution plan is built directly from the parse tree representation of the query. A simple query is a DYNAMIC SCROLL or NO SCROLL cursor that does not contain any kind of subquery, more than one table, a proxy table, a user-defined function, NUMBER(*), UNION, aggregation, DISTINCT, GROUP BY, or more than one predicate on a single column. Simple queries can contain ORDER BY only as long as the WHERE clause contains conditions on each of the primary key columns.

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.

You can force the database server to optimize a simple query, or to optimize a stored procedure or user-defined functions that contains a query, by using the OPTION FORCE OPTIMIZATION clause in the SELECT statement. When this clause is specified, plan caching is not used. See SELECT statement.