Database Reference
In-Depth Information
As shown in Figure
6-2
, the following are the key components:
Parser:
This is the first component involved in the execution of a SQL statement. Its
purpose is to deliver to the query optimizer a parsed representation of the SQL statement.
Additional information about the work the parser performs is provided in Chapter 2,
specifically in the “How Parsing Works” section.
Logical optimizer:
During the logical optimization phase, the query optimizer
produces new and semantically equivalent SQL statements by applying different query
transformation techniques. The purpose of the logical optimizer is to select the best
combination of query transformations. In doing so, the search space is increased,
and execution plans can be explored that wouldn't be considered without such query
transformations. Later in this chapter, in the “Query Transformations” section, I provide
additional information about the work performed by this component.
Physical optimizer:
During the physical optimization phase, several operations are
performed. At first, several execution plans for each SQL statement resulting from the logical
optimization are generated. Then every one of them is passed to the cost estimator to let it
calculate a cost. Finally, the execution plan with the lowest cost is selected. Simply put, the
physical optimizer explores the search space to find the most efficient execution plan.
Cost estimator:
Based on the inputs introduced in Figure
6-1
, the cost estimator calculates
the cost for the execution plan submitted by the physical optimizer.
Row source generator:
The execution plan produced by the query optimizer can't be
directly executed by the execution engine. It has to be converted into a tree of row source
operations and stored in the library cache.
Execution engine:
This component executes the row source operations produced by the
row source generator. If monitoring for cardinality feedback is active, the execution engine
verifies (after execution) whether actual and estimated values differ significantly. If a
significant difference is found, information about the correct values is stored in the shared
SQL area, and a re-optimization on the next execution is forced.
Query Transformations
The query optimizer uses a multitude of query transformations to produce new and semantically equivalent SQL
statements. Among those query transformations, depending on the method used to decide whether they are applied,
two approaches can be distinguished:
Heuristic-based query transformations
are applied when specific conditions are met.
They're expected to lead to better execution plans in most situations.
Cost-based query transformations
are applied when, according to the cost computed by
the cost estimator, they lead to execution plans with lower costs than the original statement.
The following sections introduce two dozen query transformations and provide examples of their use. The
aim is not to describe them extensively but simply to give you an idea of what's going on under the hood during the
logical optimization phase. Therefore, no detailed information about prerequisites or limitations is given. Also note
that some query transformations are more powerful or only available in recent releases. Hence, not every query
transformation described in this chapter is available in earlier releases, such as 10.2 and 11.1.
