Re: Oracle Won't use INLIST ITERATOR in simple case

<jim.thompson_at_lagosantafe.com> wrote in message news:1162955456.184901.24490_at_h54g2000cwb.googlegroups.com...
>
> I found that the parameter for index vs. tablescan was set too high.
> I don't have the doc in front of me right now, but I set it to 20 and
> it works correctly now.
>
> I'll do some more experimentation with it.
>
> Thanks for responses to this.
>

You probably mean optimizer_index_cost_adj,

It isn't a parameter for "index vs. tablescan", it's a parameter that affects the relatively costing of a single block read taking place through an indexed access path with the cost of a multi-block read.

Since you're on 9.2, you should be working out how to use system statistics (CPU costing) which allows Oracle to use a timing strategy to compare ALL single block reads with multiblock reads, rather than relying on a hack that was introduced in Oracle 8.

See also:
  http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html