Advantages of SQL Rule-Based Optimization

The Oracle "Rule-based Optimizer" (RBO) has been deprecated. Oracle began deprecating the RBO with the release of Oracle 9i, which was introduced in the early 2000s. Since then, Oracle has focused on the "Cost-based Optimizer" (CBO), which uses statistical information about the data to determine the most efficient way to execute SQL queries. The Rule-based Optimizer was officially desupported in Oracle 10g, making the Cost-based Optimizer the standard for query optimization in Oracle databases. Oracle's emphasis on the CBO reflects its greater flexibility and efficiency in handling diverse and complex database environments, particularly as databases scale in size and complexity. Oracle recommends that users rely on the CBO and maintain up-to-date statistics for optimal performance.

We need to remember to separate the hype from the reality of the SQL optimizers. While Oracle publicly states that the cost-based optimizer^[1] is superior to rule-based optimization, experienced DBAs know that the rule-based optimizer will often make a faster execution plan. The safest way to test SQL is to always try the rule hint whenever you are experimenting with alternative execution plans. After all, even in Oracle8, Oracle application products continue to utilize the rule-based optimizer. While the cost-based optimizer may eventually replace the rule-based method, it is not dead by any means.

The rule-based SQL optimizer (RBO) was generally not more efficient than the cost-based optimizer (CBO) even prior to Oracle 11g. Here's why:

1. Limited Intelligence: The RBO primarily relied on a fixed set of rules and heuristics. It lacked the CBO's ability to analyze database statistics (table sizes, index information, data distribution). This often led to suboptimal execution plans, especially for complex queries.
2. CBO Advantages: Even in earlier versions, the CBO was capable of a more nuanced analysis of the data and possible query paths.
3. Situational Performance: In certain very specific and simplistic scenarios, the rule-based optimizer might have had a slight edge due to its lower initial overhead. However, for the majority of real-world queries, CBO would outperform it.
4. Predictability vs. Efficiency: The RBO had an advantage in terms of predictability of the chosen query plan, which is sometimes desirable for legacy systems. However, predictability came at the cost of performance.

Why Oracle Introduced RBO Historically

• Early databases had limited statistics gathering and processing power. Simpler RBO algorithms were sufficient for the queries of the time.
• As databases grew in size and complexity, the CBO became essential for efficient query optimization.

The rule-based optimizer may have been better for queries that have very dynamic data, and the statistics may be obsolete. In a cost-based environment where the data characteristics are rapidly changing, the rule-based optimizer may have made a better access decision. The cost-based optimizer is improving with every release of Oracle. As it continued to improve, rule-based optimization was deprecated with Oracle 11g.

[1] Cost-Based Optimizer (CBO): The Oracle Cost-Based Optimizer (CBO) is a component of the Oracle database that analyzes various possible execution plans for an SQL query. It leverages statistics about the data (e.g., table size, data distribution) to estimate the "cost" of each plan and chooses the one it deems most efficient.