Carle, William T (Bill), NLCIO wrote:
Howdy,
I have a query which was taking an extremely long time
to complete. The OPTIMIZER_MODE in the init.ora file is set to CHOOSE,
meaning it will use the ALL_ROWS method to determine its access paths. I
Bill,
Besides statistics and how they are gathered, and other information, there
are numerous parameters that influence the CBO and it's decisions. And just
to clarify, you said the query ran in under a second when using FIRST_ROWS
(and thus a nested loops indexed lookup approach if possible).
Title: RE: FIRST_ROWS vs. ALL_ROWS
Hi Bill,
It's been a while since I messed with the optimizer but I'll take a stab.
FIRST_ROWS is for something like forms, where returning something quickly, even if it is just a few rows, is important.
ALL_ROWS is meant for throughput. The execution
Hello again,
I want to thank everyone for responding to my query. I have a much
better understanding of how this works now. I changed my query to remove the
rownum 5 and the query returned 344066 rows. The output of the tkprof is
below:
FIRST_ROWS
call count cpuelapsed
