Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???
Try setting
optimizer_max_permutations = 1000
in stead of the default 80000. Can be done at session level to test out
whether this has any ill effects. My production system has been running with
this in init.ora these last six last months.
Regards, Hans Henrik Krohn
"David Sisk" <davesisk_at_ipass.net> skrev i en meddelelse
news:oagR6.61462$i56.19595649_at_typhoon.southeast.rr.com...
> I have a particular query which uses a function in the WHERE clause, and
> I've created a function-based index for it (Oracle8.1.7). With the
> OPTIMIZER_GOAL = CHOOSE or ALL_ROWS, it doesn't use the index, but with it
> set to FIRST_ROWS, the optimizer does choose the function-based index.
This
> is what we want because the particular query executes about 2500 times
> faster when using the function-based index (0.002 seconds vs. 6 seconds).
>
> I tried leaving OPTIMIZER_GOAL=CHOOSE and setting
OPTIMIZER_INDEX_COST_ADJ=
> some low value, and the optimizer chose the same execution plan, but it
took
> several seconds to do so. I'm just wondering if anyone else has noticed
> this unusual (or it seems so to me) behavior.
>
> Please post or email!
>
> Regards,
> Dave
>
>
>
Received on Thu May 31 2001 - 02:46:17 CDT