Re: Tuning - using more than one partition

"Volker Hauswurz" <volker.hauswurz_at_materna.de> wrote in message news:du4e47$s71$1_at_pentheus.materna.de...
>
>>
>
> Thanks for the quick response.
> This what we see in detail:
>

    ....

>
> We do not understand why the plan completely changes when we use three
> tables instead of two (and it seems to be a bad plan...)
>
> Thanks
>
> Volker
>
>

I am assuming that your test is very artificial to try and demonstrate a point. However, one feature of your data is that the volumes are very small. It is possible that the if the volume if data in the partitioned table were very much larger then the plans would be more as you expect.

You could try the following hint to see the cost of the plan you are expecting:

    leading (v1.temp v2.temp v3.temp v1.test v2.test v3.test)

I'm not 100% sure that this should work, and you may need to do a rewrite using inline views so that you can specify query block names, but I think it will give you:

    3 table Cartesian merge on the tmps,     then
    3 consecutive partition range iterators on the tests

-- 
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