Understanding Oracle cost-based optimizer (CBO) and rule-based optimizer (RBO)

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database backup and recovery How to export triggers in an Oracle export command How to precreate Oracle table extents and define extent size How to trim the Oracle listener log in Unix/Linux How to avoid invalid objects in Oracle when restoring the database How to perform an Oracle 9i upgrade with the Database Upgrade Assistant How to avoid Oracle error ORA-00060 when dropping a datafile in Oracle 10g How to precreate tablespaces in Oracle before a new database import How to solve an Oracle import error How to use Transportable Tablespaces in Oracle to copy files quickly How to use Oracle table extents to allocate less disk space Oracle database administration How to rebuild a built-in Oracle package body How to use a wrapper script with cold backups in Oracle How to rebuild a database to change Oracle block size Can we use a single database update trigger for an Oracle database upgrade? The top 10 Eye on Oracle blog posts of 2008 The top advice from Oracle experts in 2008 Web 2.0 users, community and participation in the enterprise Enterprise search and links for Web 2.0 Tips for Web 2.0 success and setting Web 2.0 goals in the enterprise New Web 2.0 tools: Beyond the basics Oracle database administration Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

When you submit a query, Oracle often has multiple methods to execute the query. "Should I use an index or not? Which join method should I use?" Before Oracle can execute the query, it has to determine the answers to questions like these to determine an optimal execution path.

Older versions of Oracle used a set of rules to decide which execution path was the most "optimal." One rule said, if an index exists, use it. These rules were ranked, so some rules trumped other rules. All of this was called the rule-based optimizer (RBO).

Now think of a table with only two rows of data in it and an index on the table. It really doesn't make much sense to use the index when querying this table because doing so involves extra work. Just read the one block in the table containing the two rows and ignore the index. However, the old RBO would do the extra work because the RBO was not smart enough to understand that the table had so few rows so as to make the index inefficient. This concept brought us the cost-based optimizer (CBO). The CBO would make decisions based on the data distribution, the number of rows in the table, etc. The CBO needs information about the tables and indexes in order to make well-informed decisions. To help out the CBO, we calculate statistics on the tables and indexes that the CBO uses. The RBO does not use these statistics.