Oracle Optimizer: Moving to and working with CBO - Page 5

4. Why is RBO being removed?

Oracle 9i release 2 will be the last version that officially supports RBO. Oracle recommends all partners and customers to certify their applications with CBO before this version is no longer supported. Though RBO will be available in Oracle 10i, it will no longer be supported.

As per a published Oracle note, the existence of RBO prevents Oracle from making key enhancements to its query-processing engine. Its removal will permit Oracle to improve performance and reliability of the query-processing components of the database engine.

Presently, Oracle support for RBO is limited to bug fixes only and no new functionality will be added to RBO.

5. Why move to CBO?

Key benefits that come to mind:

1. Oracle stopped developing for RBO environment a long time back.

2. RBO will subsequently be removed from the Oracle database.

3. RBO has a limited number of access methods compared to CBO.

4. All the new features require CBO. CBO is enabled to identify these features, and how to evaluate their cost. Most of these features will be of importance for any setup; e.g. Index organized tables, bitmap indexes, Function-based indexes, reverse-key indexes, Partitioning, Hash joins, Materialized views, parallel query, star joins, etc.

5. Metalink Support.

Once RBO is no longer supported, Oracle support will not be available.

6. CBO has matured.

Prior to Oracle 7, RBO could outperform CBO in some situations. Moreover, CBO would not behave as expected and often choose bad execution plans. CBO has been improved across releases and today it is a much better alternative considering the benefits and advances towards new features.

7. Distributed and remote queries are more reliable.

In RBO, it was difficult to fine tune queries that used database links and has tables from both local and remote database. CBO outperforms RBO in this regard. In CBO, the local optimizer is aware of the statistics present in the remote table and is able to make better decisions on execution plans. RBO may not consider indexes on remote databases, but CBO has access to statistics and information regarding indexes on a remote database and can decide on an execution plan.

» See All Articles by Columnist Amar Kumar Padhi