SQL statement tuning and the differences between RBO and CBO is quite a topic. Hopefully, the following will get...
you started. But more study will be necessary as this is only a cursory glance at the subject.
Whenever Oracle receives a SQL statement, it must decide the best manner in which to retrieve the data from the database. It is the job of the Optimizer to determine the most efficient method to use. There could be many different access paths to the data. In the earliest versions of Oracle, the Optimizer used a set of rules (RBO) to decide how to execute a given SQL statement. One rule stated than an index would be used in favor of a full table scan. So let's look at an example table of employees:
EMP -------------------- ID NUMBER PRIMARY KEY NAME VARCHAR2(30) GENDER CHAR(1)On the EMP table, I have an index on the ID column and an index on the GENDER column. Since the ID is unique, querying for a specific ID can be most often facilitated by using the index. The Rule Based Optimizer (RBO) would always use this index to perform the query. Now let's query the same table for all females (GENDER='F'). The RBO, using the same rule, would use the index on this column to execute the query. Assuming equal distribution, the same number of men and women in the company, an index would be less effecient than simply scanning the entire table. Let's further assume that this table only has five rows of data that all fit comfortably in one database block, which requires only one I/O operation to read. Reading an index block, even for the ID column, would be more inefficient than performing a full table scan. SQL statement tuning when using RBO came down to the DBA obtaining an understanding of the data distribution and determining the best access path not found according to the RBO. The DBA would then rewrite the SQL statement to use the rule the DBA determined was most efficient.
The RBO has its share of problems as the above examples show. The RBO did not take into account data distribution or the size of the table. The Cost Based Optimizer (CBO) uses this type of information to make better informed decisions. The CBO needs statistics to be able to make informed decisions on the quickest access paths. SQL statement tuning under the CBO involves making sure that the correct statistics are in place, and even locking down statistics so that changes to the data do not affect the SQL statement processing. Additionally, the DBA can make use of Stored Outlines so as to get the same consistent access path for the queries. The DBA can still use SQL hints to influence the CBO as well, similar to the RBO.
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.