Rule Based Optimizer   «Prev  Next»

Lesson 3Historical appeal of rule-based optimization
ObjectiveExplain the historical appeal of RBO and distinguish legacy motivations from supported modern tuning decisions.

Why Rule-Based Optimization Was Used

Optional historical reference: Rule-based optimization belongs to the history of Oracle SQL tuning. Understanding its appeal helps explain inherited coding conventions and application expectations. It does not make the rule-based optimizer (RBO) a supported alternative for modern Oracle databases.

This lesson considers what practitioners could find useful about a known set of rules and familiar execution behavior. It does not claim to reconstruct Oracle's original engineering decisions. A documented mechanism can help explain a technology's attraction without proving why every developer adopted it.

Oracle documented RBO desupport beginning with Oracle Database 10g Release 1 (10.1). Its guide distinguished unsupported functionality from removal: RBO still existed in that release, but was unsupported. That historical statement does not establish its internal implementation status in current releases.

A decision model that practitioners could study

RBO used predefined access-path and join-selection rules together with information about available schema objects and the SQL statement. A practitioner could study those preferences and relate some plan choices to an index, predicate, or other known feature of the query.

This offers a reasonable explanation for part of its historical appeal: a familiar set of rules could make certain decisions easier to discuss. The explanation is an interpretation of the decision model, not evidence that all users preferred it or that the rules always produced a good result.

The preceding historical RBO overview explains the process and its limitations. In particular, simple slogans about the last table in the FROM clause do not capture the full join-selection procedure. Familiarity with a simplified rule was not the same as understanding every case.

The cost-based optimizer[1] evaluates alternatives through estimates. That different model can require investigating data characteristics and estimates as well as the SQL and available objects. Greater familiarity with an older model should not be mistaken for proof that the newer model is unsuitable.

Established applications created expectations

An application may have been tested with particular plans, data volumes, and response-time expectations. Its maintainers could reasonably want evidence before changing behavior that appeared to work. This concern helps explain why old conventions can survive after their original technical setting has changed.

For example, a team may inherit a query with a comment explaining a particular table order. The comment might record a successful historical workaround, an incomplete explanation, or an assumption no longer relevant to the current database. Its presence alone does not establish which interpretation is correct.

A useful review records the intended outcome and compares it with current behavior. Does the caller consume all rows? Does it stop after an initial batch? Have the relevant tables grown substantially? Those questions make the investigation concrete without assuming that every inherited convention must be either preserved or removed immediately.

Predictable rules did not guarantee predictable performance

There are several meanings of predictability. A rule can be understandable, a plan can appear familiar, and a measured execution can be repeatable. These are related observations, but none automatically establishes the others.

A familiar plan can process much more data after an application grows. Different input values can produce substantially different result sizes. Changes to available schema objects can also affect which access paths are possible. Knowing a rule does not guarantee that its consequences remain suitable for every execution.

Likewise, a historical report that one RBO plan performed well does not establish that RBO generally outperformed cost-based optimization. Such a comparison needs the release, statement, data, environment, and measurement conditions. Without that context, broad claims about which optimizer was faster are anecdotes rather than transferable guidance.

The opposite blanket claim is also unhelpful. There is no need to insist that every old rule-selected plan was slow. A plan could suit its original workload while the optimizer that selected it was later desupported. Historical usefulness and current support are separate questions.

Independence from statistical costing had limits

RBO did not compare plans using the statistical cost model associated with CBO. This distinction could make the planning model appear simpler to someone accustomed to its rules. It did not mean the database had no schema information, or that a rule-selected plan was unaffected by the amount of work encountered during execution.

Nor does it justify avoiding statistics maintenance today. If current estimates poorly represent a workload, investigate the relevant statistics, predicates, and execution evidence within supported cost-based optimization. Missing statistics do not turn modern Oracle into the historical RBO.

Rapidly changing data also requires investigation rather than an automatic optimizer switch. A query that returns a few rows for one input may process many more for another. Changing a goal or adding a hint does not itself supply accurate information about those differences.

Separate historical attraction from present-day guidance

Interpreting the appeal of an older optimizer model
Historical attractionWhat it did not guaranteeModern implication
Known rule preferencesThe best plan for every query and dataset.Explain a plan using its operations, estimates, and observed work.
Familiar application behaviorContinued suitability as data and requirements changed.Validate representative cases before retaining or changing assumptions.
No statistical cost comparisonFreedom from performance problems caused by data characteristics.Investigate estimate quality within supported cost-based planning.
Successful historical workaroundsA reusable recommendation for current releases.Record the original purpose and evaluate the current requirement.

Evaluate inherited advice through evidence

Begin with correct results and a recorded baseline. Identify the actual SQL, relevant settings, important input values, and fetching behavior. Inspect the executed plan where possible; an estimated plan alone does not measure application performance.

Then compare a focused, supported alternative under comparable conditions. Include common cases and important larger results. Distinguish initial response time from complete fetching, and consider whether cache state or concurrent activity could explain a difference.

Preserve a way to restore temporary changes and document the outcome. Removing an old directive does not promise that every plan remains unchanged. Keeping an old convention also needs a reason beyond its age. The aim is to understand the application's current needs and the evidence supporting the chosen behavior.

Do not follow old advice to always try a RULE hint. Current work should use supported optimization behavior. The Module 6 review explains modern goals, scope, and verification. The next lesson considers historical limitations of rule-based optimization.

[1] Cost-based optimizer: An optimizer that uses available information to estimate the work of candidate plans and select among the alternatives considered. Estimated cost is not measured runtime.

Oracle references


SEMrush Software 3 SEMrush Banner 3