Oracle wont use Index with IN operator

[zephan]

Hi All,
I've got a table table1 (a varchar2(6), b date).
An index idx1 on column a.
When I use a clause where with IN operator

Select *
from table1
where a in ('X', 'Y', 'Z');

idx1 is not used event with an optimizer hint.
But if I turn it to = operator.

Select *
from table1
where a = 'X';

idx1 index is used.

What should I do to force oracle use INLIST iterator and then the index.
BR,
Zephan

 

[cmmrfrds]

Try using a >= along with the list.


Select *
from table1
where a >= 'X' or a in ('X', 'Y', 'Z');

 

[carp]

It's entirely possible you really don't want Oracle to use the index; at least, that's the conclusion your optimizer has come to. If using the index would actually make your query less efficient, then the optimizer will ignore it.

What percentage of your rows have "a IN ('X','Y','Z')".
How many total rows are in the table - and how many blocks are they spread across? These are all factors the optimizer looks at.

Finally, when was the last time you gathered statistics on the table so the optimizer has an accurate picture of your data?

 

[Turkbear]

Hi,
To see if it is actually the IN statement, change your query to:

Select *
from table1
where 
(a ='X' 
or
a = 'Y' 
or
a = 'Z')

Is still no index use, re-read carp 's posting...




To Paraphrase:"The Help you get is proportional to the Help you give.."