the rule-based SQL optimizer
Today in Oracle9i, both
the cost-based optimizer and the rule-based optimizer are commonly used to
derive the execution plan for Oracle SQL statements. Back in the
1990’s Oracle announced plan to retire the rule-based optimizer, but it
remains today as an important tool for optimizing Oracle SQL.
optimizer is very elegant for its simplicity and often made faster
execution choices than the CBO. In fact, Oracle Applications products used
the RBO until 2001 when the Oracle Apps 11i product was introduced. It is
only with the release of Oracle8i (8.1.6) that the CBO has become faster
than the RBO in all cases.
While it is very
tempting to go into the relative advantages of the RBO and CBO within each
successive release of Oracle, we can make some general observations about
the characteristics of the rule-based optimizer:
The biggest shortcoming
of the RBO is that it will common choose the “wrong” index to access a
table. This is because the RBO does not have statistics to tell it
the relative selectivity and cardinality of the indexes column.
If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: