Index usage

[manmaria]

If I have 'in' in the sql, then my query uses the index or it does a table space scan?

For example my query is like

select col1,col3
from tabl1
where tabl1.col2 in
(select tabl2.col5 from tabl2)

I have index on col2 for the tabl1. But my question is whether this query uses the index which I have in col2?

Thank you,
Manmaria

 

[BillDHS]

I believe that if you do an explain, the sql will appear as a series of "OR"'s. I think an index can be used since this is an = value, but this does not mean that TD will use the index.

 

[tdatgod]

run the explain and see what it says.....

explain
select col1,col3
from tabl1
where tabl1.col2 in
(select tabl2.col5 from tabl2)



----

 

[dnoeth]

The optimizer will flatten the subquery into a join.
It will only use a Primary Index to join (unless it's a Nested Join plan, which will never occur for that kind of query).
If it's using the PI then there's no preparation step (spool) for that table in explain...

Dieter