Difference between CBO and RBO?
In the history of Oracle Database performance, the transition from RBO to CBO was like moving from a rigid, paper map to a real-time GPS with live traffic updates.
If you are maintaining an older system or studying for certification, understanding the shift from "Rules" to "Costs" is essential.
The Rule-Based Optimizer was the standard in the early days of Oracle (pre-version 7). It followed a set of 15 rigid rules ranked in order of priority.
How it worked: The RBO looked at the available paths (like "Is there an index?") and chose the one with the highest ranking rule.
The Ranking: A "Unique Index lookup" (Rank 1) always beat a "Full Table Scan" (Rank 15).
The Blind Spot: It didn't care about the size of your data. To the RBO, a table with 5 rows and a table with 5 billion rows were treated exactly the same if they both had an index.
The CBO is the intelligent engine used in all modern versions (Oracle 10g through 23c). It doesn't follow a fixed set of ranks; instead, it performs mathematical simulations.
How it works: It looks at the "Cost" of various execution plans. Cost is an estimate of the computer resources (I/O, CPU, and Memory) needed to do the job.
The GPS Analogy: Just as Google Maps might suggest a longer highway route because the shorter side street is clogged with traffic, the CBO might choose a Full Table Scan over an Index if the statistics show it would actually be faster.
| Feature | Rule-Based Optimizer (RBO) | Cost-Based Optimizer (CBO) |
| Logic | Fixed set of 15 "Rules." | Mathematical "Cost" estimation. |
| Data Awareness | Blind. Doesn't know how many rows exist. | Aware. Relies on data statistics. |
| Flexibility | Rigid. Hard to tune. | Dynamic. Adapts as data grows. |
| I/O & CPU | Ignores hardware performance. | Considers CPU and I/O speeds. |
| Status | Obsolete/Deprecated. | The Standard for 20+ years. |
The CBO is superior because it understands the distribution of your data.
Imagine a table called SESSIONS with a column STATUS.
99% of the rows are "INACTIVE."
1% of the rows are "ACTIVE."
If you search for "INACTIVE" rows:
RBO: Sees an index on STATUS and uses it. This is slow because reading 99% of a table via an index is much worse than just scanning the whole table.
CBO: Sees the statistics, realizes "INACTIVE" is almost the whole table, and chooses a Full Table Scan, which is significantly faster.
The CBO is only as smart as the information you give it. It requires Optimizer Statistics (metadata about row counts, block counts, and data distribution).
If your statistics are stale (e.g., the database thinks the table is empty but it actually has millions of rows), the CBO will make poor decisions. This is why running DBMS_STATS is the most important maintenance task for a DBA.
Technically, the RBO code was removed from the Oracle kernel years ago. While you can sometimes see a hint of it in "Rule" hints, Oracle effectively ignores it. If you don't gather statistics, the CBO will simply use "Dynamic Sampling" (guessing based on a small sample of the data) rather than reverting to the RBO.
You can see which "goal" your optimizer is currently pursuing by checking the OPTIMIZER_MODE parameter:
SHOW PARAMETER OPTIMIZER_MODE;
Values like ALL_ROWS (default) or FIRST_ROWS tell the CBO whether it should focus on total throughput or getting the first few results back as fast as possible.