Oracle Database - The Query Plan Generator

Card Puncher Data Processing

About

The main function of the plan generator is to try out different possible plans for a given query and pick the one that has the lowest Oracle Database - The Query Plan Estimator.

The plan for a query is established by first generating subplans for each of the nested subqueries and unmerged views. Each nested subquery or unmerged view is represented by a separate query block. The query blocks are optimized separately in a bottom-up order. That is, the innermost query block is optimized first, and a subplan is generated for it. The outermost query block, which represents the entire query, is optimized last.

Many different plans are possible because of the various combinations of different :

that can be used to access and process data in different ways and produce the same result.

Number of possible plans

The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders. The number of possible plans for a query block is proportional to the number of join items in the FROM clause. This number rises exponentially with the number of join items.

The plan generator uses an internal cutoff to reduce the number of plans it tries when finding the one with the lowest cost. The cutoff is based on the cost of the current best plan. If the current best cost is large, then the plan generator tries harder (in other words, explores more alternate plans) to find a better plan with lower cost. If the current best cost is small, then the plan generator ends the search swiftly, because further cost improvement will not be significant.

The cutoff works well if the plan generator starts with an initial join order that produces a plan with cost close to optimal. Finding a good initial join order is a difficult problem.





Discover More
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Query Optimizer Process
Oracle Database - The Query Plan Estimator

The estimator is involved in the query optimizer process. Its main task is to measure the plans that give the plan generator. It generates three different types of measures : These measures...
Query Optimizer Process
SQL Engine - Query Optimizer (Query Optimization)

in a SQL Engine. A SQL statement can be executed in many different ways, such as: full table scans, index scans, nested loops, hash joins. The query optimizer determines the most efficient...



Share this page:
Follow us:
Task Runner