Re: Why the optimizer sux..or sux I ?

ajung_at_suxers.de wrote:
>
> Help me to believe in the Oracle 8i CBO:
> Assume the following table:
>
> table ojs_main(docnum number primary key ,heading char(4));
>
> The "heading" column is also index.
> When I do a simple search like:
>
> select docnum from ojs_main where heading='CONS' or heading='PARL'
>
> I get an optimal execution plan by using a range scan on the index
> of the "heading" column. When I change the query to
>
> select docnum from ojs_main where heading='CONS' or heading='PARX'
>
> the optimizer suddenly uses a full table scan for the table ojs_main
> with much more consistent gets and physical reads. The complete
> table and all indexes are full analyzed.
>
> So can someone explain me this behaviour ?
>
> Andreas

If 'PARX' is close to being one of the extrema for that column, then Oracle may have made the "intelligent" decision that the range of data to be returned is "large" and thus avoided the index.

A histogram may assist, but you may be better off just using bind variables anyway, ie

where column between :b1 and :b2

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse