Re: CHOOSE and ALL_ROWS
- From: Stefano Cislaghi <s.cislaghi@xxxxxxxxx>
- To: gabriel.aragon@xxxxxx
- Date: Tue, 26 Oct 2010 08:13:09 +0200
Hi,
FIRST_ROWS and ALL_ROWS change the optimizer work to gain a different
result... in particular with ALL_ROWS, according to the manual, optimizer
will look up fot the plan which uses up minimal resource to process all rows
affected by the statement.
ALL_ROWS afaik is the standard method used by the optimizer, FIRST_ROWS_n is
a modified version that should force the optimizer to retrieve quickly the
FIRST_n rows for a statement.
You mention to ALL_ROWS for a DWH is maybe related to the whole working
environment. A DWH with his report services or business intelligence usually
expect to retrieve all rows for a statement because the single query (that
of course may be really long and complex) produces the results expected.
A normal application in an OLTP environment many times expect to get for a
single query the first results that are shown on video, maybe first 20 rows
for the first page of your application.
Be carefull in changing this value and do many tests. Metalink as few
interesting documents, check note: *372431.1.*
Stefano
--
http://www.stefanocislaghi.eu
Other related posts:
