Q: What is the difference between Rule based optimization and Cost based optimization in Oracle SQL queries?

Rule based optimizer or RBO uses a set of rules to determine the execution plan for a given query. For example if there is an index on one of the columns used in where clause, then RBO will always use the index. The biggest problem with RBO is that it does not take the distribution of data into consideration and hence sometimes ends up slowing down the query.
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 everytime a query uses status in the where clause. If such a query returns 50% of the table 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. Using 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 otherhand If a query uses status='N' the index shall be used.