Rule Based Optimizer and Cost Based Optimizer

What is the difference between Rule based Optimizer and Cost based Optimizer in oracle?

In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db.

A long time ago the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically the RBO used a set of rules to determine how to execute a query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example assume someone put an index on the GENDER column which holds one of two values MALE and FEMALE. 

Then someone issues the following query: 

SELECT * FROM emp WHERE gender 'FEMALE'; If the above query returned approximately 50 of the rows then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its rules said to. 

It became obvious that the RBO armed with its set of discrete rules did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table its indexes and the data distribution to make better informed decisions. 

Using our previous example assume that the company has employees that are 95 female and 5 male. If you query for females then you do not want to use the index. If you query for males then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO. 

What is Cost-Based Optimization? 

The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it canít reach do this without good, up-to-date statistical information on the data being accessed. The optimizer can use a rules-based approach to work without statistical information, but this approach is less intelligent than the cost-based approach. With the rules-based approach, the optimizer chooses an execution plan based a set of rules about what types of operations usually execute faster than other types. With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema objects (tables, clusters, or indexes) being accessed. 

Rule Based Optimizer Obsolescence

The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this osolescence are:

- The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported. 
- The default value for the OPTIMIZER_MODE parameter is ALL_ROWS. 
- The CHOOSE and RULE optimizer hints still exist but are no longer supported. 
- Code requiring the RBO must be migrated to use the query optimizer. 

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.