User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 25. Query Optimization
The role of the optimizer is to devise an efficient way to execute the SQL statement. 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, and the order in which the tables are to be read.
Often, a great number of plans exist that all accomplish the same goal. Other variables may further enlarge the number of possible access plans.
A single statement can contain multiple subqueries. A portion of an access plan that describes how to satisfy a single subquery, including table permutation and access methods, is called a join strategy. When a subquery refers to many tables, the number of possible join strategies can become very large. For example, if seven tables must be joined to execute a subquery, then the optimizer must select one of the 7! = 5040 orders in which these tables could be accessed. It must also decide which index, if any, to use when accessing each table.
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 that each will require. 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 Anywhere optimizer devises a suitable access plan based only on the semantics of each statement.
Syntactic differences, although they may appear 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 is conscious of the resources it is using. It periodically 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.
The governor is the part of the optimizer that performs this limiting function. It lets the optimizer run until it has analyzed a minimum number of strategies. Once a reasonable number of strategies have been considered, the governor cuts off further analysis.
In the case of expensive and complicated queries, the optimizer will work longer. In the case of very expensive queries, it may run long enough to cause a discernable delay.