Lesson 8	Effective tuning tips for rule-based optimization
Objective	Evaluate and tune rule-based queries.

Transitioning from Rule-Based to Cost-Based Optimization in Oracle SQL Tuning

For many years, Rule-Based Optimization (RBO) guided how Oracle determined execution paths for SQL statements. Under this legacy system, the optimizer relied on fixed precedence rules rather than data statistics. Developers manually ordered tables, predicates, and indexed columns to influence query performance. While this approach offered predictability, it lacked adaptability as data volumes grew and workloads diversified. Today, Oracle’s Cost-Based Optimizer (CBO) fully replaces RBO, leveraging data-driven insights for intelligent, self-adjusting query optimization.

Understanding the Legacy Rule-Based Approach

The RBO followed a strict set of internal ranking rules to determine how queries should be executed. Each table and condition was assigned a priority, with the optimizer making choices based on the position of tables in the FROM clause and the order of Boolean expressions in the WHERE clause. Tuning under RBO required procedural discipline, ensuring the SQL statement was written in the most favorable order for Oracle’s rule hierarchy.

1. Evaluating Tables in the FROM Clause

FROM clause: Determine the driving table First, evaluate the order of the table names in the FROM clause. — Determine the driving table first by evaluating the order of table names in the FROM clause.

In RBO, the order of tables within the FROM clause mattered significantly. The driving table—the one Oracle reads first—was ideally the table that produced the fewest rows. Placing this table last in the FROM clause allowed Oracle to process filters efficiently, minimizing I/O operations.

2. Using the WHERE Clause to Identify the Driving Table

Then look to the WHERE clause to see what table will return the smallest number of rows. This table will become the driving table, and it will be placed last in the FROM clause. — Use the WHERE clause to find the table returning the smallest number of rows—this becomes the driving table.

The WHERE clause played a pivotal role in determining which table returned the fewest qualifying rows. The “most selective” table—that is, the one producing the smallest dataset—became the logical driving table for efficient filtering and join processing.

3. Ordering Boolean Conditions by Selectivity

Check the WHERE clause. Order all of the Boolean conditions in the WHERE clause from most-selective to least-selective. This will signal the rule-based optimizer to use the most selective index. — Order Boolean conditions from most-selective to least-selective to guide the optimizer.

Under RBO, developers manually ordered Boolean conditions within the WHERE clause so that highly selective filters—those eliminating the greatest number of rows—appeared first. This structure helped the optimizer use the most efficient index available and reduce unnecessary reads.

4. Ensuring Indexed Columns in Filters

Check to ensure that all items in the WHERE clause are indexes. — Ensure all items in the WHERE clause are indexed.

Performance gains under RBO relied heavily on indexes. Developers verified that all columns in the WHERE clause were indexed, since non-indexed filters forced full table scans, significantly degrading query performance.

5. Validating Execution Paths with EXPLAIN PLAN

Run ALL queries through EXPLAIN PLAN to ensure that you are using the optimal path. — Run all queries through EXPLAIN PLAN to verify the optimal execution path.

To confirm that Oracle followed the intended access path, developers executed each query using EXPLAIN PLAN. This utility visualized the optimizer’s steps, helping identify costly full table scans, improper join orders, or missing indexes.

6. Locking Behavior with SQL INDEX Hints

Once tuned, use an SQL INDEX hint to ensure that the proper index will always be used to service the query. — Use SQL INDEX hints to enforce index usage for consistent query performance.

Once a query performed optimally, developers often added INDEX hints to ensure Oracle consistently used a specific index. While this approach improved stability in RBO environments, it limited flexibility—one of the key reasons RBO became obsolete.

The Evolution to Cost-Based Optimization (CBO)

Modern Oracle databases, including Oracle 19c and 23ai, exclusively use the Cost-Based Optimizer. Unlike RBO, which depends on static rule sets, CBO relies on table and index statistics stored in the data dictionary. The optimizer calculates the estimated cost of multiple execution plans and selects the one with the lowest resource usage. CBO supports advanced features such as:

Adaptive Query Optimization – dynamically adjusts execution plans during runtime.
SQL Plan Management – preserves known-good plans and prevents regressions.
Automatic Workload Repository (AWR) – collects and analyzes performance metrics over time.

Transitioning from RBO to CBO represents a shift from manual tuning to intelligent automation. Modern performance tuning focuses on maintaining accurate statistics, leveraging DBMS_STATS, and using EXPLAIN PLAN or AUTOTRACE to validate optimizer decisions rather than manually ordering SQL clauses.

In summary, while Rule-Based Optimization provided a structured foundation for SQL tuning in earlier Oracle releases, today’s databases thrive under the Cost-Based Optimizer. Developers now focus on data quality, indexing strategy, and query design, allowing Oracle’s optimizer to make informed, cost-efficient decisions automatically.