Question:
What is the difference between the all_rows
and first_rows optimizer modes? I know that all_rows
and first_rows are optimizer goals but I don't understand
how the difference can be between the best execution plan
for all rows and the best execution plan using first_rows
SQL optimization?
Answer: While that the
many subtle differences between all_rows vs. first_rows
optimization, and are some well-defined differences in
behavior:There are several definitions of best" when
optimizing SQL and that is the correct forbearance between
all_rows and first_rows:
- first_rows: Oracle generates
a SQL plan that
returns the first rows as
quickly as possible (favors index access). Unlike
all_rows, the first_rows optimization is there to
minimize transaction response time, for small
transactions in OLTP systems. In first_rows mode,
The Oracle
cost-based optimizer will favor an index scan, even if
it has a higher overall cost than a full-table scan.
- all_rows: The difference is that
this is the plan that returns
the last rows as quickly as
possible (favoring parallel full-table scans).
Unlike first_rows optimization, the all_rows
goal is to maximize throughput while minimizing resources
consumption, That's another big difference:
all_rows may return no rows at all until all of the
the rows have been fetched,
The important thing to remember about the
difference between all_rows and first_rows
optimization of that the default of all_rows is
not appropriate for most databases, and that the
first_rows optimizer rmode pays an extra I/O
cost(the cost of using an index) to get the rows
back quickly.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
