Introduction
In this chapter, we discuss the query optimizer, which is a built-in component of the Oracle database. The optimizer chooses the most efficient way to execute a SQL statement, using three steps:
Query transformation.
Estimation.
Plan generation.
The query transformation step accepts the parsed statement, divides it into query blocks (for example, identifying a subquery), and determines if it's better to transform the query blocks into a different SQL statement—semantically equivalent—that can be processed in a more efficient way.
The estimator determines the overall cost of an execution plan, based on selectivity, cardinality, and the cost of each operation involved in the plan. If statistics are available, the estimator uses them for computation, improving the accuracy of the result.
The plan generator explores various plans for each query block, due to various factors: different access paths, joins, and/or join order. We can follow many paths to answer a query; the plan generator...
