Re: Partitions - performance problem

There is a note on Metalink about some parameters that may help. I am not sure how well they work with IN subqueries, as the note (I think) discusses only joins - but your IN subquery can be re-written as an in-line view (or Dan Morgan's suggested WITH subquery).

The parameters (shown with their defaults) are

    _subquery_pruning_enabled = true
    _subquery_pruning_cost_factor = 20
    _subquery_pruning_reduction = 50

Essentially, if the driving subquery is expected to return less than a ceratin percentage of the rows (the sp_pruning_reduction) from the driving table then Oracle will run a sampling query against the driver to find the list of partitions needed if the sample query is low enough (less than cost of not eliminating / sp_cost_factor)

Brute forcing:

    set the cost factor to 1
    set the reduction to 100

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<chrilleboy76_at_hotmail.com> wrote in message 
news:1146838292.536448.29740_at_j33g2000cwa.googlegroups.com...


> 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

>