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_at_gmail.com> 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_at_wp-sa.pl> 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_at_wp-sa.pl>
> > WP/PTI/DIP/ZAB (+04858) 52 15 770
> > MySQL v. 4.x
> > Oracle v. 10.x
> > ---------------------------------------
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>

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