Cost Based Optimizer Vs Rule 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.

CBO and RBO are the optimization strategies used by Database engines for executing a query or a stored procedure. They come into picture after a query or Stored Procedure is compiled and is just about to execute (most databases also cache these generated execution plans).

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 is 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. 

Source: http://www.erpgreat.com/oracle-database/rule-based-and-cost-based-optimizer.htm