Rule Based Optimizer   «Prev  Next»

Lesson 1

Oracle Rule-Based Optimizer: Historical Context

Optional historical reference: This lesson explains terminology and assumptions found in older Oracle applications and tuning guides. Modern Oracle SQL tuning uses cost-based optimization. The rule-based optimizer, or RBO, is not a supported alternative to select for a new application.

Understanding this history can help you interpret inherited SQL without repeating obsolete advice. The purpose is to recognize why an old application was written a particular way, then evaluate its requirements using supported techniques. For current optimizer goals and their practical use, see the Module 6 optimizer-modes review.

What the historical rule-based optimizer did

The RBO selected execution strategies using predefined access-path rules and information about the available schema objects. An access path is a way of obtaining rows, such as using an index or scanning a table. A complete execution plan combines access paths with the other operations needed to carry out the SQL.

Unlike cost-based planning, the historical approach did not compare alternatives through a statistical cost model describing the workload. Its rule rankings could make the availability of an access path especially important. Older advice therefore often focused on how SQL was written and which indexes existed.

That background explains why a developer might have followed a rule that now looks arbitrary. It does not establish that the rule remains effective in a current database. Before preserving or removing an inherited convention, identify what it was intended to accomplish and whether current execution evidence supports it.

When Oracle stopped supporting RBO

Oracle documented RBO desupport in Oracle Database 10g Release 1 (10.1). Its Performance Tuning Guide explicitly stated that the functionality still existed in that release but was unsupported. The same notice identified RULE and CHOOSE parameter values and hints as unsupported.

Desupport is different from physical removal. The notice does not justify claiming that every internal RBO implementation disappeared in 10g. It does establish why a modern course should not teach RBO as a supported production choice. The relevant milestone is 10g Release 1, not an assertion that desupport began with 11g.

See Oracle's 10g Release 1 Performance Tuning Guide, the RBO Obsolescence notice on printed page xxix, for the historical support statement.

Why RULE and CHOOSE appear in older material

RULE identified the historical rule-based approach. CHOOSE belonged to the period when statistics and release-specific behavior affected the selection between rule-based and cost-based optimization. Those names are useful clues to the age and assumptions of a configuration example.

They should not be presented alongside current optimizer goals as equivalent choices for new tuning. Modern settings such as ALL_ROWS and the numbered FIRST_ROWS values express performance goals within cost-based optimization. Missing object statistics do not cause modern Oracle to select the historical RBO.

Keep a distinction between documenting an old setting and recommending it. A migration investigation may need to record legacy hints or initialization parameters. That inventory is evidence about the application being maintained, not an instruction to introduce those settings into another environment.

Recognize clause-order folklore without adopting it

Older RBO tuning material often discusses arranging table names in the FROM clause or predicates in the WHERE clause. Such advice belongs to its original optimizer and release context. The textual arrangement of SQL is not a general guarantee of join order or predicate evaluation order in modern Oracle.

For current work, inspect the execution plan rather than inferring execution order from the order in which conditions are written. Readability is a useful reason to organize SQL consistently, but a cosmetic rearrangement is not proof of a performance improvement.

Similarly, rewriting Boolean expressions with UNION is not an automatic tuning rule. Query semantics and duplicate handling must remain correct. A rewrite that changes the result is not an optimization, and a logically suitable rewrite still requires measurement. Detailed transformation examples belong in a separate lesson with explicit assumptions.

How modern cost-based planning differs

The current optimizer uses available information, including optimizer statistics, to estimate the work of candidate plans. It selects among the alternatives it considers. Statistics describe characteristics of the data; they do not require the optimizer to read every actual row before planning each query.

For example, an index may be useful when a predicate selects a small fraction of a table, while a scan can be appropriate when much of the table is needed. Neither operation is universally better. The complete plan also includes the cost of obtaining table rows, joining results, and performing required processing.

Estimated cost is not a measured elapsed time. Better information can help planning, but neither refreshed statistics nor a changed optimizer setting guarantees an improvement. Compare representative executions and investigate substantial differences between estimates and observed work where suitable execution statistics are available.

See Oracle's current Query Optimizer Concepts for the cost-based planning model. Module 6 explains how to distinguish that model from the performance goals expressed by optimizer modes.

Put historical advantages in context

Older discussions sometimes praise RBO for simplicity or predictable rule rankings. These are historical motivations, not guarantees of stable execution time. A familiar plan can become unsuitable as data volumes and application requirements change.

The absence of statistical costing also does not make unsupported RBO a modern solution for avoiding statistics maintenance. Claims that it is preferable for small databases or limited resources need specific historical evidence and should not become general recommendations.

Cost-based optimization should not be oversold either. It does not guarantee that every plan is best, that every query adapts while executing, or that parallel processing will improve every workload. These are separate questions that require an appropriate environment and evidence.

Use this history when reviewing inherited SQL

  1. Identify the assumption. Record the legacy hint, configuration note, or coding convention and its intended purpose.
  2. Establish the current requirement. Determine whether the application needs an initial batch, complete result consumption, or another measurable outcome.
  3. Inspect current behavior. Check the relevant environment and executed plan. An estimated plan alone does not measure execution performance.
  4. Evaluate supported alternatives. Use representative values and realistic fetching behavior, with a recorded baseline and a way to restore temporary changes.
  5. Document the decision. Explain what changed, why, and what evidence supports retaining or retiring an inherited convention.

This process avoids assuming that removing an old hint preserves every plan. It also avoids treating an old plan as the permanent target regardless of present requirements. Correct results and measured application behavior provide the basis for the decision.

What you should take from this reference

Return to the modern optimizer review for practical fundamentals. The next page, historical rule-based optimizer concepts, belongs to this legacy reference material and should be read in that context.


SEMrush Software 1 SEMrush Banner 1