ASA SQL User's Guide
Query Optimization and Execution
How the optimizer works
The steps the Adaptive Server Anywhere optimizer follows in generating a suitable access plan include the following.
The parser converts the SQL query into an internal representation. It may rewrite the query, converting it to a syntactically different, but semantically equivalent, form. For example, a subquery may be rewritten as a join. These conversions make the statement easier to analyze.
Optimization proper commences just before execution. If you are using cursors in your application, optimization commences when the cursor is opened. Unlike many other commercial database systems, Adaptive Server Anywhere optimizes each statement just before executing it.
The optimizer performs semantic optimization on the statement. It rewrites each SQL statement whenever doing so leads to better, more efficient access plans.
The optimizer performs join enumeration for each subquery.
The optimizer optimizes access order.
Because Adaptive Server Anywhere performs just-in-time optimization of each statement, the optimizer has access to the values of host variables and stored procedure variables. Hence, it makes better choices because it performs better selectivity analysis.
Adaptive Server Anywhere optimizes each query you execute, regardless of how many times you executed it before, with the exception of queries that are contained in stored procedures or user-defined functions. For queries contained in stored procedures or user-defined functions, the optimizer may cache the access plans so that they can be reused.
For more information, see Access plan caching.
Because Adaptive Server Anywhere saves statistics each time it executes a query, the optimizer can learn from the experience of executing previous plans and can adjust its choices when appropriate.
If a query is recognized as a simple query, a heuristic rather than cost-based optimization is used—the optimizer decides whether to use and index scan or sequential table scan, and builds and executes the access plan immediately. Steps 4 and 5 are bypassed.
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, user defined functions, 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 primary key column.