Rule Based Optimizer   «Prev  Next»

Lesson 4Disadvantages of rule-based optimization
ObjectiveExplain how fixed rules can overlook data distribution and why modern tuning requires cost estimates and execution evidence.

Disadvantages of Rule-Based Optimization

Oracle's legacy rule-based optimizer (RBO) ranked available access paths using predefined rules. That made its decisions understandable to people familiar with the hierarchy, but it left out an important question: how much work would a particular path require for the data actually stored in the table?

This lesson is historical reference. Oracle 26ai uses cost-based optimization; RBO is not a current tuning strategy. The employee example below explains a limitation of fixed rankings without assuming that every index access is better than a full table scan. It also shows why a cost-based estimate needs appropriate statistics and should be checked against execution evidence.

One table, two indexes, different distributions

Assume an EMPLOYEE table contains 100,000 rows, with separate nonunique indexes on STATUS and DEPARTMENT. In this example, status value 3 means retired. This is an application convention, not a status code defined by Oracle.

Employee table with separate status and department indexes: 50,000 retired employees, 5,000 Finance employees, and 100 matching both conditions.
Finance matches 5% of employees, retired status matches 50%, and both conditions match 0.1%. These counts illustrate selectivity, not measured execution cost.
Employee distribution used in this example
StatusFinanceOther departmentsTotal
Active4,90045,10050,000
Retired10049,90050,000
Total5,00095,000100,000

The query requests the names of retired employees in Finance:

SELECT e.emp_name
FROM employee e
WHERE e.department = 'FINANCE'
  AND e.status = 3; -- Retired in this example

Exactly 100 rows meet both conditions in the stated dataset. This SQL is illustrative; no database execution or benchmark is represented here. It involves one table, so the question is access-path selection, not join order. Writing one condition before the other in the WHERE clause does not prescribe its execution order.

Selectivity reveals what a fixed ranking misses

Predicate selectivity is the fraction of rows that satisfy a condition. Finance matches 5,000 of 100,000 rows, or 5%. Retired status matches 50,000 rows, or 50%. Therefore, Finance is the more selective condition: it identifies a smaller part of the table.

A rule that ranks two available index paths without considering these distributions cannot use this tenfold difference in candidate counts to estimate their relative work. Two columns can both have usable indexes while identifying very different populations.

Two hypothetical separate-index access paths
Initial accessMatching index entriesRemaining checkFinal rows
STATUS index: retired50,000Department is Finance100
DEPARTMENT index: Finance5,000Status is retired100

The table describes candidate paths, not captured Oracle execution plans. For these separate indexes, accessing table rows may be necessary to check the remaining condition and retrieve employee names. The counts are not buffer gets, physical reads, or elapsed times. They do not prove a tenfold speedup, nor do they establish that RBO would always choose STATUS and a modern optimizer would always choose DEPARTMENT.

Why the most selective index does not always win

Selectivity helps estimate work, but it does not describe all of it. Table rows reached through an index may be concentrated in a few blocks or scattered across many blocks. The cost of those table accesses can change the comparison substantially. Index size, requested columns, and the amount of data read also matter.

A full table scan can consequently be a reasonable choice. A different index structure might offer another candidate path. Modern optimization compares estimated costs among alternatives rather than applying a universal instruction to use the smallest matching population. The goal is an efficient complete plan, including the work needed after the initial access.

Related columns complicate cardinality estimates

The example also illustrates a challenge for cost-based optimization. Knowing that 50% of employees are retired and 5% work in Finance does not reveal how many satisfy both conditions. If these proportions were treated as independent, the calculation would be:

100,000 × 0.50 × 0.05 = 2,500 estimated rows

The supplied dataset contains only 100 matching rows. Retirement status and department are related in this dataset, so the simple independence assumption misses that relationship. The optimizer does not automatically know the exact intersection merely because this lesson states it.

Oracle supports column-group statistics to help estimate predicates involving related columns. These can improve cardinality estimates, but they do not guarantee exact counts or the same plan for every environment. This distinction matters: replacing fixed rules with cost estimates makes data distribution relevant, while the quality of the available information still influences the result.

Legacy workarounds to retire

Older tuning advice sometimes tried to discourage an index by changing an expression's datatype. Such rewrites can change query meaning. For example, 3 || 0 produces the string '30'; it does not append a null character. Comparing a numeric status column with that expression can compare against 30 through implicit conversion, rather than the intended value 3.

Changing a predicate is also different from marking an index unusable. Do not disable an index simply because one statement appears to use it poorly: other statements and maintenance requirements may depend on it. Preserve query correctness while investigating the actual access-path problem.

Hints can influence optimizer decisions, but an index hint is not evidence that the selected path performs better. Likewise, ALL_ROWS expresses a throughput objective; it does not guarantee a particular index. These mechanisms should not replace investigation of estimates and observed work.

Evaluate the evidence in a modern database

Start with the statement's intended result and representative data. Examine whether relevant statistics describe the table and its distributions. Then compare estimated row counts with actual row counts from an appropriately instrumented execution, and assess work such as buffer activity and elapsed time under comparable conditions. An estimated plan alone is not a record of what a statement actually did.

The historical lesson is that fixed rankings could overlook differences that matter to performance. The current lesson is to test the assumptions behind a cost estimate before prescribing a remedy. The next lesson discusses FROM-clause positioning in legacy RBO; read that behavior as historical context, not as a modern rule for arranging SQL text.

References

SEMrush Software 4 SEMrush Banner 4