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
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.