Problem solve Get help with specific problems with your technologies, process and projects.

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

Oracle expert Brian Peasland explains how use Oracle RBO, CBO and statistics when executing a query.

What is the relationship between Oracle CBO, RBO and statistics?
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.

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.