How the optimizer works

The role of the optimizer is to devise an efficient way to execute SQL statements. To do this, the optimizer must determine an execution plan for a query. This includes decisions about the access order for tables referenced in the query, the join operators and access methods used for each table, and whether materialized views that are not referenced in the query can be used to compute parts of the query. The optimizer attempts to pick the best plan for executing the query during the join enumeration phase, when possible access plans for a query are generated and costed. The best access plan is the one that the optimizer estimates will return the desired result set in the shortest period of time, with the least cost. The optimizer determines the cost of each enumerated strategy by estimating the number of disk reads and writes required.

In Interactive SQL, you can view the best access plan used to execute a query by clicking Tools » Plan Viewer. See Reading graphical plans, and Reading execution plans.

 Minimizing the cost of returning the first row
 Using semantically equivalent syntax
 Reducing the cost of optimizing queries

Optimizer estimates and column statistics
Automatic performance tuning
Underlying assumptions of the optimizer
Using predicates in queries
Cost-based optimization with MIN and MAX functions
Plan caching