Failure of rule-based Optimization

One of the first things the Oracle Remote DBA looks at is the default optimizer mode for their database. There are two classes of optimizer modes:

(RBO) the rule-based optimizer and
(CBO) the cost-based optimizer.

The Oracle init.ora parameters offer four values for the optimizer_mode parameter.

optimizer_mode = RULE

The first, and oldest optimizer mode is RULE. Under the rule-based optimizer, Oracle uses heuristics from the data dictionary in order to determine the most effective way to service an Oracle query and translate the declarative SQL command into an actual navigation plan to extract the data. In many pre-Oracle8i systems, rule-based optimization is faster than cost-based. In fact, Oracle Applications used rule-based optimization until release 11i.

optimizer_mode = FIRST_ROWS

This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources. The FIRST_ROWS optimizer mode usually involves choosing a full index scan over a parallel full table scan. Because the FIRST_ROWS mode favors index scans over full table scans, the FIRST_ROWS mode is most appropriate for inline systems where an end user wants to see some results as quickly as possible.

optimizer_mode = ALL_ROWS

This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full table scan over a full index scan. Because the ALL_ROWS mode favors full table scans, the ALL_ROWS mode is best suited for batch-oriented queries where intermediate rows are not required for viewing.
To illustrate the difference between ALL_ROWS and FIRST_ROWS with an oversimplistic example, consider the following query:

1) Assume that we have a table called EMPLOYEE with an index on the DEPARTMENT column

2) Let us further assume that there are only three departments on our database, 1) accounting, 2) finance, and 3) marketing.

3) Let us assume that accounting and finance has 2,500 employees each, but the marketing department has 10,000 employees. In other words, the rows are not equally distributed among the departments.

4) Now we issue the following SQL

5) In this case, the fastest method for returning these rows is a full-table scan because two-thirds of the rows in the table are being requested by the query. Because the rule-based Optimizer does not know the distribution of values, it will choose an index range scan to service this query. When two-thirds of the rows are being accessed, an index range scan will be slower than a full-table scan.