Rule Based Optimizer   «Prev  Next»

Lesson 2Overview of the rule-based optimizer
ObjectiveDescribe major features of the rule-based optimizer.

Overview of Rule-based optimizer

When we use rule-based optimization, we do not have any information about the nature of the data in our table or indexes. Specifically, we do not know important information such as the number of rows in the tables or the distribution of values within an index. However, we do know the types of indexes that are defined for the table, and all of the metadata in the Oracle dictionary. Using this metadata, the rule-based optimizer will choose an execution plan that uses an index whenever one is appropriate.

Hierarchy of rules

In addition, the rule-based optimizer will formulate the execution plan according to the following hierarchy of rules.
Hierarchy of rules

Hierarchy is used by Oracle

This hierarchy is used by Oracle’s rule-based optimizer to speed access times. Note that the full-table scan is at the bottom if this list. In most cases, this is correct, but we know that there are specific instances when a full-table scan can be faster than in index range scan. (Such as when we access more than 70 percent of the table rows, or when there are very few rows in the table.) Let us examine the actual steps used by the rule-based optimizer. The following Slide Show will illustrate the process.

Rule Based Optimizer Process
Now that we see the overall flow, let us turn our attention to some of the benefits of rule-based optimization.

Rule Based Optimizer Concepts - Exercise

Click the Exercise link below to test your knowledge of the rule-based optimizer.
Rule Based Optimizer Concepts - Exercise