Why the optimizer sux..or sux I ?

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 Received on Tue Jul 18 2000 - 00:00:00 CDT