🧩Rule-Based Optimizer

NEW

A query optimizer that selects execution plans using predefined rules and heuristics instead of statistical cost models.

DESCRIPTION

A rule-based optimizer (RBO) transforms queries via deterministic rewrite rules and selects plans based on heuristics. Historically used in early relational systems, RBOs prioritize transformations like predicate pushdown and join ordering without consulting data statistics. While predictable and simple, RBOs often underperform on skewed or large datasets. Vendors like Oracle deprecated RBO in favor of cost-based optimizers (CBO). Modern systems still rely on rule-based rewrite layers, but final plan selection typically uses costs. Frameworks like Calcite provide extensible rule sets. Understanding RBO helps interpret plan transformations and legacy behavior. Hybrid approaches blend rule determinism with cost-driven search.

ALTERNATIVE DEFINITIONS

Oracle Database Concepts

The rule-based optimizer chooses an execution plan based on a fixed set of rules.

Defines RBO by fixed rules rather than statistics or cost.

Source

Oracle Database Performance Tuning Guide

Oracle no longer supports the rule-based optimizer; the cost-based optimizer determines the best plan based on statistics.

Contrasts RBO with CBO and notes deprecation.

Source

RELATED TERMS

Rule-Based Optimization Query Planner Oracle RBO Apache Calcite Cascades Framework Volcano Optimizer SQL Optimization Heuristic Optimization Relational Algebra

COMPETING TERMS

Cost-Based Optimizer

RELATIONS

• Broader: Optimizer

KNOWLEDGE AREAS

Data Engineering Data Storage & Operations Software Engineering

TARGET AUDIENCE

Data Engineer Software Developer Data Architect BI Specialist

EXTERNAL SOURCES

• Wikipedia - Query optimizer
• Oracle - Optimizer overview and deprecation
• Apache Calcite - Rule-based rewrite framework
• Volcano Optimizer (paper) - Foundational optimizer theory

TOP QUESTIONS

Why did vendors move away from Rule-Based Optimizers?

Cost-based approaches adapt to data distributions via statistics, delivering better performance and plan quality at scale.

Category: Opinion

What are typical heuristics in a Rule-Based Optimizer?

Apply predicate pushdown, prune projections, reorder joins based on simple heuristics, and perform normalization.

Category: Example

What’s the biggest risk with Rule-Based Optimizers?

They can choose poor plans under skewed data or complex joins, significantly degrading performance.

Category: Risks

Is Rule-Based Optimizer still relevant?

Rule-based rewrites remain fundamental, but most systems combine them with cost-based search for final plan selection.

Category: Relevancy

How can I identify if a system uses RBO?

Check documentation and plan explanations; RBO lacks statistics-driven decisions and relies on deterministic rules.

Category: Management

What’s a competing term for Rule-Based Optimizer?

Cost-Based Optimizer, which evaluates candidate plans using data-derived cost estimates.

Category: Alternatives

Can Rule-Based Optimizers be hybridized?

Yes, systems often combine rule transformations with cost-based exploration of candidate plans.

Category: Explanation

Where to learn about Rule-Based Optimizers?

Read Oracle optimizer docs and the Volcano paper; explore Calcite for practical rule sets.

Category: WhereToStart

What is a striking analogy for Rule-Based Optimizers?

They’re like a cookbook: fixed recipes (rules) guide transformations, but they don’t taste the dish (cost) before serving.

Category: Example