With nearly 50% of
production systems using rule-based SQL optimization (RBO), this is big for
Oracle. As you migrate to Oracle10g, you may need to undertake a
migration from RBO to CBO. This may involved re-writing rule-based
SQL, especially queries where you rely on the ordering of the table names in
the FROM clause to determine the driving table.
Also, remember that
you will be able to adjust the all-important parameter
optimizer_index_cost_adj to a small number (< 10) to make the CBO simulate
the behavior of the RBO.
Below is a short
excerpt from the MOSC note:
Desupport of the
Rule-Based Optimizer
The rule-based optimizer (RBO) will no longer be supported when Oracle9i is
de-supported. The release after Oracle9i (referred to in this article as
Oracle10g) will only support the cost-based optimizer (CBO). Hence Oracle9i
Release 2 is the last release to support the RBO. Partners and customers
should certify their applications with the CBO before that time. The CBO has
been Oracle's primary optimization method for many years now, and most major
independent software vendors use the CBO by choice. Our surveys indicate
that over 80% of customers are using the CBO with Oracle8i, and this number
is expected to increase with Oracle9i. For more information on the CBO
please refer to the Query Optimization in Oracle9i white paper which can be
found at:
http://otn.oracle.com/products/bi/pdf/o9i_optimization_twp.pdf
What is being Desupported?
The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and
earlier. The RBO is being desupported in Oracle10g. The RBO will still exist
in Oracle10g, but will be an unsupported feature and will be removed in a
future release. No code changes will be made to the RBO code and no bug
fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has
continued to be available for backwards compatibility. Oracle Oracle10g will
support only one optimizer, and all applications running on that release
should use that optimizer.
Versions Affected
Support for the RBO will be removed in Oracle10g. The last release that
supports the rule-based optimizer will be Oracle9i Release 2.
Why is the RBO being Desupported?
The existence of the RBO prevents Oracle from making key enhancements to its
query-processing engine. The removal of the RBO will permit Oracle to
improve performance and reliability of the query-processing components of
the database engine. Furthermore, using the RBO prevents customers from
taking advantage of all the query-processing technologies introduced since
Oracle 7.3. For example, the RBO cannot take advantage of partitioned
tables, bitmap indexes, hash join, parallel query, index organized tables,
function-based indexes, materialized views and many others. As a result,
customers never realize the benefits and the superior performance gained by
these techniques.
and include the URL for the page.
