RE: How to tune this query:

Hi Gene

> Thank you for your thoughts. I thought that the explain plan
indicated
> that the index OFFR_ALT8 will
> be read only for 1 partition based on this line in my explain plan:
>
> |* 14 | INDEX FAST FULL SCAN | OFFR_ALT8 | 20 |
480
> |
> 3 | KEY | KEY |
>
> I thought that KEY KEY indicated that it will only look at one
partition.
> Am I misinterpreting that?

Yes. The KEY KEY indicates that the pruning will be performed at runtime. This kind of access is chosen when at parse time it is not possible to determine which partition(s) will be accessed. To know how many partitions will be read you have to know how many times that specific operation will be executed and with which partition key. Notice that at runtime it is also possible that Oracle re-read the same partition multiple times.

Now, to know how many times that specific operation is executed, you have to look at its parent operation. In this case it is the PARTITION RANGE ITERATOR, which only provides the information that the optimizer expects to go through multiple partitions.

So, you have to have a look at the parent of PARTITION RANGE ITERATOR. In this case it is the NESTED LOOPS. Since the PARTITION RANGE ITERATOR is the second operation of the NESTED LOOPS (a.k.a. inner loop), it is executed for *each* row produced by the first operation (a.k.a. outer loop). In this the first operation is the MERGE JOIN CARTESIAN.

Therefore if the MERGE JOIN CARTESIAN returns, for example, 42 rows, the index fast full scan is basically executed 42 times.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l