Re: Partitions - performance problem

chrilleboy76_at_hotmail.com wrote:
> 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

And your Oracle version is?

If 10g I'd suggest rewriting your query using WITH or forcing unnesting as possibilities to explore.

For a WITH query demo go to Morgan's Library at www.psoug.org and look up WITH CLAUSE.

Daniel Morgan
www.psoug.org Received on Fri May 05 2006 - 16:46:12 CDT