Optimizer Modes
You can specify which optimizer to use and how to use the cost-based optimizer by selecting from the following optimizer modes:
- RULE
The rule-based optimizer is used.
- CHOOSE
If there are no statistics for any table in the query, the rule-based optimizer is used. If there are statistics for some of the tables in the query, the optimizer uses those statistics and guesses at the statistics for the rest of the tables.
- ALL_ROWS
The cost-based optimizer chooses the execution path that will return all the rows in the query the fastest. This mode is most appropriate for data warehousing queries.
- FIRST_ROWS[( n )]
The cost-based optimizer chooses the path that will return the first n rows that satisfy a query the fastest. The n portion of the keyword, introduced with Oracle9i, can have a value of 1, 10, 100, or 1000. This mode is most appropriate for OLTP type queries, where there may be user interaction after some rows are retrieved.
These modes can be set in various ways. You can set the initialization parameter OPTIMIZER_MODE. You can set the mode interactively, using the command:
ALTER SESSION SET OPTIMIZER_GOAL = optimizer_modeOr you can specify a hint in an individual SQL statement, as explained in the next section.
Tip
Although you still have the choice of which optimizer mode to use, we recommend that you always use the cost-based optimizer. Although the cost-based optimizer got somewhat of a bad name in its initial releases, it has been significantly improved in the years ...