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 perform Oracle database recovery with a corrupt online redo log Can I create an Oracle Catalog Database with the Enterprise Console? Do I need to recreate views after an Oracle table reorg? Client-based apps vs. web-based apps in Developer 2000 How to perform an Oracle 8i to 10g migration Can I move the tablespace online with missing datafiles in Oracle? Oracle upgrade process vs. Oracle exp/imp for 9i to 10g migration How to use RMAN CONVERT to migrate from Solaris to Oracle 11g RHEL Why am I having trouble installing Oracle 10g on Vista? Can I load Oracle 8 on Windows Server 2003? 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.