Lesson 7	Oracle rule-based optimizer
Objective	Describe the Functions of Oracle rule-based Optimization

Oracle rule-based Optimization

The Rule-Based Optimizer (RBO) in Oracle Database, unlike the Cost-Based Optimizer (CBO), does not rely on data statistics and cost evaluations to determine the most efficient execution plan for a query. Instead, the RBO uses a pre-defined set of rules to make this determination. In that sense, it can be considered to use a form of "heuristics."
These rules, or heuristics, are based on the structure of the SQL statement and the specific operations involved, such as the type of join used or whether an index is available. The RBO prioritizes operations based on these rules, irrespective of the data distribution, the size of the tables, or the existence of indexes.
For example, some of the heuristics used by the RBO include the following:

An equality predicate on a unique key or primary key is given top priority.
An equality predicate on a non-unique key is ranked next.
Range scans on indexed columns follow.
Full table scans are considered last.

These heuristics are static, and the RBO does not adapt to changing data or workload characteristics. This is in contrast to the CBO, which dynamically adjusts its decisions based on data statistics and estimates of the cost of different operations.
It's important to note that Oracle has deprecated the RBO and no longer supports it as of Oracle Database 11g. Oracle strongly recommends using the CBO, which provides a more adaptable and efficient approach to SQL optimization based on the characteristics of the data and the workload.
In conclusion, while the Oracle Rule-Based Optimizer can be said to use heuristics in the form of predefined rules to determine the access path for SQL statements, it's no longer the recommended or supported method for query optimization in Oracle databases. The Cost-Based Optimizer, using data statistics and cost estimations, is the current standard.

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.

Simple Set of Access Rules

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 series of images describe an example of such a failure.

1) Assume that we have a table called EMPLOYEE with an index on the DEPARTMENT column

2) Let us assume that there are only three departments on our database — 2) Let us further assume that there are only three departments on our database, 1) accounting, 2) finance, and 3) marketing.

3) Let us assume that accounting and finance has 2,500 employees each, but the marketing department has 10,000 employees. In other words, the rows are not equally distributed among the departments.

5) In this case, the fastest method for returning these rows is a full-table scan because two-thirds of the rows in the table are being requested by the query. Because the rule-based Optimizer does not know the distribution of values, it will choose an index range scan to service this query. When two-thirds of the rows are being accessed, an index range scan will be slower than a full-table scan.

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.