| Lesson 4 | Disadvantages of rule-based optimization |
| Objective | Explain how fixed rules can overlook data distribution and why modern tuning requires cost estimates and execution evidence. |
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.
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.
| Status | Finance | Other departments | Total |
|---|---|---|---|
| Active | 4,900 | 45,100 | 50,000 |
| Retired | 100 | 49,900 | 50,000 |
| Total | 5,000 | 95,000 | 100,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.
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.
| Initial access | Matching index entries | Remaining check | Final rows |
|---|---|---|---|
| STATUS index: retired | 50,000 | Department is Finance | 100 |
| DEPARTMENT index: Finance | 5,000 | Status is retired | 100 |
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.
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.
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.
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.
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.