Advantages of SQL Rule-Based Optimization

We need to remember to separate the hype from the reality of the SQL optimizers. While Oracle publicly states that the cost-based optimizer 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.

Because of the simplicity of the rule-based SQL optimizer, the rule-based optimizer often makes more efficient decisions about an execution plan than the cost-based optimizer.
The rule-based optimizer normally makes better decisions about the access path under the following conditions:
Complex equi-joins of more than three tables
The rule-based optimizer will perform better than the cost-based for table joins of more than three tables.
The cost-based optimizer has a very bad habit of performing a full-table scan on a table whenever more than three tables are joined together.

The rule-based optimizer may be 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 make a better access decision. The cost-based optimizer is improving with every release of Oracle. If it continues to improve, rule-based optimization will probably become obsolete. However, this is still a long time off, since even many Oracle8 applications written by Oracle (Oracle Financials, GEMMS), still use the rule-based optimizer
Now that you know two situations where it’s a good idea to use the rule-based optimizer, let’s look at instances when the rule-based optimizer is not the best solution.