Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from Oracle in a Nutshell

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_mode

Or 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page