RE: How to tune this query:
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 20:08:05 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
