Re: Tuning - using more than one partition

"Volker Hauswurz" <volker.hauswurz_at_materna.de> wrote in message news:du49f8$joi$1_at_pentheus.materna.de...
> Hello,
>
> we are using
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
> Production
>
>
>
> -- Here we see PARTITION RANGE (ITERATOR) 2 times which is what we
> expected
> -- so at runtime just the important partitions get to be visited
>

    No, we don't see anthing.

> Here we get
>
> ....
>
> | 6 | PARTITION RANGE ALL | | | | | 1 | 3 |
>
> | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 | 3
>
> |* 8 | INDEX RANGE SCAN | I3 | 3 | | 4 (0)| 1 | 3 |
>
> | 9 | PARTITION RANGE ALL | | | | | 1 | 3 |
>
> | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 | 3
>
> |* 11 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 |
>
> | 12 | PARTITION RANGE ALL | | | | | 1 | 3 |
>
> | 13 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 3 | 6033 | 2 (50)| 1 | 3
>
> |* 14 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 |
>

    But it would be nice to see the joins and the     predicates in lines 8,11 and 14 at least.

You need to post both complete plans if you want to give people some chance of working out the answer to this one.

Given your example, it may simply be that the cost based optimizer has worked out that there is no benefit in trying to be clever with these tables.

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