Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: a explain plan question

Re: a explain plan question

From: amonte <ax.mount_at_gmail.com>
Date: Tue, 30 Jan 2007 16:52:17 +0100
Message-ID: <85c1fb130701300752l1fb9924agdf88a0a0326c50a9@mail.gmail.com>


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_at_centrexcc.com> 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
> ______________________________________________________________________
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 30 2007 - 09:52:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US