Re: a explain plan question

That is what I think Wolfgang, that TUD_FEDIA is accessed First.

If TUH_NVPAGINA was accessed first then I would ask!

How on Earth do you do partition elimination! Without accesing TUD_FEDIA to
get the partition keys.

But then I am puzzled because if TUD_FEDIA is accesed first and a Nested
Loop is used then doesnt that mean

1. get a row from TUD_FEDIA
2. Search the key in TUH_NVPAGINA

and so on when the row source is exahusted. In this case TU_FEDIA returns 31
rows, number of partitions read is 4.

If it is correct wouldnt it access 4 * 31 partitions?


Thanks

Alex


On 1/30/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:


At 07:43 AM 1/30/2007, amonte wrote:
>Hi
>
>I have this query

SNIP

>and this plan
>
>Execution Plan
>----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1022392 Card=934
> Bytes=49502)
>    1    0   SORT (GROUP BY) (Cost=1022392 Card=934 Bytes=49502)
>    2    1     NESTED LOOPS (Cost=814767 Card=182275095 Bytes=9660580035)
>    3    2       TABLE ACCESS (FULL) OF 'TUD_FEDIA' (Cost=3 Card=30
Bytes=480)
>    4    2       PARTITION RANGE (ITERATOR)
>    5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159
> Card=5992606 Bytes=221726422)
>

>--------------------------------------------------------------------------------------------
>| Id  | Operation                  |  Name         | Rows  | Bytes |
>Cost  | Pstart| Pstop |

>--------------------------------------------------------------------------------------------
>|   0 | SELECT STATEMENT           |               |   934 | 49502
>|  1022K|       |       |
>|   1 |  SORT GROUP BY             |               |   934 | 49502
>|  1022K|       |       |
>|   2 |   NESTED
>LOOPS             |               |   182M|  9213M|   814K|       |
|
>|*  3 |    TABLE ACCESS FULL       | TUD_FEDIA     |    30 |   480
>|     3 |       |       |
>|   4 |    PARTITION RANGE
>ITERATOR|               |       |       |       |   KEY |   KEY |
>|*  5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  5992K|   211M|
>27159 |   KEY |   KEY |

>--------------------------------------------------------------------------------------------

SNIP

>I was wondering how to read this plan, the order of steps. From old
>set autotrace trace exp it seems to me that step 5 is the first step?

Unless I'm completely mistaken, the first step is to access the
driving table of the nested loop join, i.e. step 3. Besides, if it
didn't it wouldn't have the information for the filter predicate for step
5

An interesting side-observation (for me at least) was that the M
stands for MB as in 1,048,576=1024*1024, not Million as in 1,000,000.
I wouldn't intuitively count rows in multiples of 1024.


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

Other related posts: