ASA SQL User's Guide
Query Optimization and Execution
The role of the optimizer is to devise an efficient way to execute SQL statements. The optimizer expresses its chosen method in the form of an access plan. The access plan describes which tables to scan, which index, if any, to use for each table, which join strategy to use, and what order to read the tables in. Often, a great number of plans exist that all accomplish the same goal. Other variables may further enlarge the number of possible access plans.
The optimizer begins selecting for the choices available using efficient, and in some cases proprietary, algorithms. It bases its decisions on predictions of the resources each query requires. The optimizer takes into account both the cost of disk access operations and the estimated CPU cost of each operation.
Most commands may be expressed in many different ways using the SQL language. These expressions are semantically equivalent in that they accomplish the same task, but may differ substantially in syntax. With few exceptions, the Adaptive Server Anywhere optimizer devises a suitable access plan based only on the semantics of each statement.
Syntactic differences, although they may appear to be substantial, usually have no effect. For example, differences in the order of predicates, tables, and attributes in the query syntax have no affect on the choice of access plan. Neither is the optimizer affected by whether or not a query contains a view.
The goal of the optimizer is to find a good access plan. Ideally, the optimizer would identify the most efficient access plan possible, but this goal is often impractical. Given a complicated query, a great number of possibilities may exist.
However efficient the optimizer, analyzing each option takes time and resources. The optimizer compares the cost of further optimization with the cost of executing the best plan it has found so far. If a plan has been devised that has a relatively low cost, the optimizer stops and allows execution of that plan to proceed. Further optimization might consume more resources than would execution of an access plan already found.
In the case of expensive and complicated queries, the optimizer works longer. In the case of very expensive queries, it may run long enough to cause a discernible delay.