Rule-based optimization vs. Cost-based optimization in Oracle

Rule-based optimization

• This is an old technique. RBO uses a set of rules to determine the execution plan for a given query. E.g. If an index is available on a table, the RBO rules can be to always use that index.
• The biggest problem with RBO is that it does not use statistics about data and hence sometimes ends up slowing down the query.

Cost-based optimization

• Motivation behind CBO is to come up with the cheapest execution plan available for each SQL statement.
• The cheapest execution plan is the one that will use the least amount of resources (CPU, Memory, I/O, etc.)

Example

For example if a table has a column status that can have only 2 values Y or N. Now if there is an index on this column, then the index shall be used every time a query uses status in the where clause. If such a query returns 50% of the table data then use of the index slows down the query execution.
The Cost based optimizer or CBO uses statistics about the table, indexes and the data distribution in them to make better decisions. Let consider the previous example, if the status column has 90% Y and 10% N. If a query uses status='Y' the index shall not be used. On the other hand If a query uses status='N' the index be used.