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.
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:
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.
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.
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.