Try OpenEdge Now
skip to main content
SQL Development
Optimizing Query Performance : Understanding optimization : Optimizer phases

Optimizer phases

The optimization process is divided into several phases. Some phases deal with internal infrastructure, such as minimizing data handling or temp-table usage. Others deal with significant cost factors and are straightforward to understand. Each phase addresses a specific type of optimization, as explained in the following sections.
The optimizer follows a cost-based model. In each stage, whenever multiple alternatives are available, the optimizer estimates the cost for each and selects the cheapest. The cost computation takes into account:
*Cost metrics for operations performed by the SQL engine's storage manager and query processor components
*Index definitions
*Properties of join algorithms
*Column selectivity
*Filter factors
*Table cardinality
The following sections provide details on the optimization phases.
* Early evaluation of constant expressions
* GROUP BY optimization
* Pushing restrict operations close to the data origin
* Using indexes for restrictions
* Choosing the best index
* Predicate expressions
* Generating candidate indexes
* Selecting an index
* Providing index hints
* Join optimization
* Determining join order among adjacent join nodes
* Choosing the join algorithm
* Augmented nested loop join
* Merge join
* Nested loop join
* Sort optimization
* Eliminating redundant sorts
* Converting table scans to index bracket scans
* Indexes to evaluate MAX/MIN functions
* Index bracket scan optimization