Re: Oracle does not use an index

schonlinner_at_yahoo.com wrote:
[...]
> then Oracle does not use the index on country_code, but does a full
> table scan. I have to adjust the optimizer_index_cost_adj to 1 or 2 to
> make Oracle think that it's cheaper to use the index. But my own
> timings clearly show (using explain plan and tkprof) that using the
> index is always faster when querying country_code.
>
> Of course, such a simple query is of no concern, but the real queries
> against the system are much more complicated, use temp table
> transformations (i.e. access this dimension table using a full scan
> instead of an index range scan) etc., and then the difference between
> using the index and not using it is 4 secs against 45 secs...
>
> What other possibilities are there to "adjust" the index cost? Or is
> this simply assumed to be a bug in the cost calculation which we should
> live with?
>
> I only know of two parameters:
>
> optimizer_index_cost_adj
> optimizer_index_caching
>
> the second one does not seem to have any influence...

As far as I know (others may be able to shed more light), the default Oracle settings for these two parameters are inappropriate for most cases.

If you increase OPTIMIZER_INDEX_CACHING from its default 0 and decrease OPTIMIZER_INDEX_COST_ADJ from its default 100, your queries will probably perform better.

Yours,
Laurenz Albe Received on Fri Nov 04 2005 - 02:54:50 CST