Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 28, 2003

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

By Amar Kumar Padhi

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM