RE: a explain plan question

 

 

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

Means that Oracle are using some partitions, and then are doing a full scan
of these partitions.

 

Alex, I think you must create an LOCAL partitioned index on
TUH_NVPAGINA.FE_DIA.

 

If you have more columns that FE_DIA in your primary key, Oracle may not use
it.

 

 

You need some like this:

2    1     PARTITION RANGE (ITERATOR)
3    2       INDEX (RANGE SCAN) OF 'TUH_NVPAGINA_IDX1' (INDEX) 

 

 

greetings

 

  _____  

De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En
nombre de Polarski, Bernard
Enviado el: miércoles, 31 de enero de 2007 10:24
Para: ax.mount@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Asunto: RE: a explain plan question

 

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  <mailto: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: