Database Reference
In-Depth Information
Chapter 6
Introducing the Query Optimizer
The query optimizer is one of the building blocks of the SQL engine. Its purpose is to produce efficient execution
plans in a timely manner. The time constraint is essential because in most situations it isn't sensible to spend too
much time on the optimization phase. What does “too much time” mean? In general, the parse phase, which contains
the work performed by the query optimizer, should be much shorter than the execution phase. The only situation in
which having a parse phase longer than the execution phase is acceptable is when a cursor can be reused for many
executions. As discussed in Chapter 2, the ability to cache the shared SQL area associated with a cursor in the SGA
was also introduced for this very same purpose.
The aim of this chapter is to provide an overview of the information used by the query optimizer to carry out
its work, to describe the architecture of the SQL engine, and explain how its components interact to process SQL
statements. It also provides information about the query transformations carried out by the query optimizer.
Fundamentals
The query optimizer, to choose an execution plan, has to answer questions like the following:
•
Which is the optimal access path to extract data from each table referenced in the SQL
statement?
•
Which are the optimal join methods and join orders through which the data of the referenced
tables will be processed?
•
When should aggregations and/or sorts be processed during SQL statement execution?
•
Is it beneficial to use parallel processing?
In practice, however, the query optimizer doesn't directly answer these questions. Instead, it explores the so-called
search space
, which consists of all potential execution plans, in pursuit of the optimal execution plan. To decide which
execution plan is the optimal one, the query optimizer estimates the cost of a number of execution plans and picks the
one with the lowest cost. For example, the search space of the following query is composed of more than a hundred
possible execution plans. With the
search_space.sql
script I was able to reproduce 122 of them. Their cost goes from
20 to more than 100,000:
SELECT *
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.n = 1 AND t2.n = 2
Because the goal of the query optimizer is to find the cheapest execution plan as quickly as possible, it is useful
that the query optimizer doesn't evaluate all execution plans for any but the simplest of SQL statements. In other
