RE: a explain plan question

Sorry my bad, now it is crystal clear.

 

Bernard Polarski

 

  _____  

From: LS Cheng [mailto:exriscer@xxxxxxxxx] 
Sent: woensdag 31 januari 2007 10:53
To: Polarski, Bernard
Cc: ax.mount@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: a explain plan question

 

Hi Bernard

In the execution plan it shows this:

|   4 |    PARTITION RANGE ITERATOR|               |       |       |
|   KEY |   KEY | 
|*  5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  5992K|   211M|
27159 |   KEY |   KEY |

Since in the parse time the partition key cannot be determined we see
the KEY words. I think it basically means tabla access full by
partition. 






On 1/31/07, Polarski, Bernard <Bernard.Polarski@xxxxxxxxxxxxxx> wrote: 

Still I don't understand why we have a PARTITION RAND followed by a full
table scan. Why not a direct full table scan, what is the advantage of
this construct

 

>    4    2       PARTITION RANGE (ITERATOR) 
>    5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159

"

On 1/30/07, LS Cheng < exriscer@xxxxxxxxx> wrote:


TUD_FEDIA is accessed first then from that it eliminates partitions
(partition start/stop KEY), the problem seems nested loop, how many rows
is TU_FEDIA returning? 
"

 

I don't see the keyword(stop key) in the plan. I read this plan and its
only speak of a partition range access that leads to a full table scan.

My only explanation is that the CBO is underlining a failed partition
pruning.

 

Bernard Polarski

  _____  

From: amonte [mailto:ax.mount@xxxxxxxxx] 
Sent: woensdag 31 januari 2007 10:09
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: a explain plan question

 

You are correct, the NL is not good, I changed to hash join and the
query runs in 50 minutes.

Thanks

Alex



On 1/30/07, LS Cheng < exriscer@xxxxxxxxx> wrote:

doesnt look very good plan 

TUD_FEDIA is accessed first then from that it eliminates partitions
(partition start/stop KEY), the problem seems nested loop, how many rows
is TU_FEDIA returning? 




On 1/30/07, Remigiusz Soko?owski < rems@xxxxxxxx <mailto:rems@xxxxxxxx>
> wrote:

        
        >
        > 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)
        >
        > 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? 
        >
        >    5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA'
(Cost=27159
        > Card=5992606 Bytes=221726422)
        >
        AFAIK the first most nested line is the first line (in this
example the
        one indicated by You) 
        
        Regards
        Remigiusz
        
        --
        ---------------------------------------
        Remigiusz Sokolowski <rems@xxxxxxxx >
        WP/PTI/DIP/ZAB (+04858) 52 15 770
        MySQL  v.  4.x
        Oracle v. 10.x
        ---------------------------------------
        
        

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

Other related posts: