SQL Tuning   «Prev 
Executing Oracle SQL

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:
  1. (RBO) the rule-based optimizer and
  2. (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:

Assume that we have a table called EMPLOYEE with an index on the DEPARTMENT column
Assume that we have a table called EMPLOYEE with an index on the DEPARTMENT column
Let us assume that there are only three departments on our database
Let us assume that accounting and finance has 2,500 employees
Now we issue the following SQL
In this case, the fastest method for returning these rows is a full-table scan