Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

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 Wed May 30 2001 - 19:23:48 CDT