> 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 --------------------------------------- -- //www.freelists.org/webpage/oracle-l