Oracle rule-based optimizer

Lesson 7

Objective

Describe the functions of 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 that can be used to modify the access plan. We will discuss hints in detail later in this course.
Hint: This is an SQL compiler directive that tells Oracle to change an execution plan.

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.

RelationalDBDesign		Home Search SiteMap
Advanced SQL Basic SQL DB Creation Architect Extended DB Features Managing DB Objects Oracle Net Topology
PL/SQL Programming SQL Tuning Database Analysis Database Design Oracle 8i PL/SQL PL/SQL Objects