SQL Tuning   «Prev  Next»

Lesson 7 Oracle rule-based optimizer
ObjectiveDescribe the Functions of Oracle rule-based Optimization

Oracle rule-based Optimization

Three rule-based heuristics

The rule-based optimizer was Oracle's first attempt at processing SQL, and dates back to the first versions of Oracle.
Oracle uses heuristics to determine the best method for accessing the data. A heuristic is a set of rules that allows the Oracle engine to guess which access path will run the fastest. These heuristics include:

Rule-based heuristics

A ranking scheme Oracle knows which access paths are faster than other access paths.
Using an index if an index exists on a target table If Oracle detects that it can use an index to access the table, the optimizer will choose that index.
Using an index will speed access to the data.
Ordering the table names in the FROM clause This rule determines the driving table. The driving table is retrieved first, and the rows from the second table are then merged into the result set from the first table. Therefore, it is essential that the second table return the least amount of rows based on the WHERE clause.

Using this simple set of access rules, Oracle usually chooses the fastest method for returning the requested rows from the query. However, sometimes the rule-based optimizer makes mistakes such as choosing the wrong index for a table. This situation led to the development of Oracle hints[1] that can be used to modify the access plan. We will discuss hints in detail later in this course.

Driving Table

The driving table should be the table with the least amount of rows returned, not just the table with the smallest total number of rows.
While the rule-based optimizer was sufficient for most queries, there were times when it failed to choose the fastest access path to the data
The following SlideShow will demonstrate an example of such a failure.

Failure Rule Bsed Optimization
Because of the shortcomings of the rule-based optimizer, Oracle knew that the best access decisions could only be made by the optimizer that was aware of the amount and distribution of the data in the target tables. Hence the cost-based optimizer was developed.
In the next lesson, the cost-based optimizer will be examined.

[1]Hint: This is an SQL compiler directive that tells Oracle to change an execution plan.