'where' with 'in' doesn't use index?
I have tables
a (a_id int, a_val varchar(60)) 4000000 rows
b (b_id int, b_val varchar(60)) 800000 rows
and indexes
a0 on a (a_id)
a1 on a (a_val)
b0 on a (b_id)
b1 on a (b_val)
select b_id from b
where b_val=(select min(concat('001',a_id)) from a where a_val like 'Ant%')
uses b1 index
select b_id from b
where b_val in (select min(concat('001',a_id)) from a where a_val like 'Ant%')
doesn't
of course, I want to use
select b_id from b
where b_val=(select concat('001',a_id) from a where a_val like 'Ant%')
I've tried
optimizer_prune_level = 0
force key (b1)
rewriting to join
but had no luck.
I'm porting from DB2 V5, it's visual explain tool shows that first temp table is created based on index a1 and then b1 is used to fetch from b.
Any idea?
Subject
Views
Written By
Posted
'where' with 'in' doesn't use index?
3157
January 24, 2006 05:25AM
2068
January 30, 2006 04:45PM
2196
February 01, 2006 06:24AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.