Re: CHOOSE and ALL_ROWS

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 26 2010 - 01:13:09 CDT