Partitions - performance problem

Hi!

I have this table, range partitioned by a column called job.

Lets say i have 50 partitions with job 10 "job"s in each When I do this :

select * from table where job = 100
the plan looks all good with something like "partition single"

and when i do:
select * from table where job in (100,101,102,103,104,105) the plan still looks all good with something like "partition single"

but when i do:
select * from table where job in (select something from some_other_table)
the plan looks kinda good with partition start = (key) and partition stop = (key) which for me looks like oracle should handle this in a smart way.
The problem is, it takes forever to run - even tho the select only returns values that should tell oracle only to look in one partition.

So my question is of course why?

Can't Oracle find out which partitions to look in when i have a "job in (select..)" ?

/C Received on Fri May 05 2006 - 09:11:32 CDT