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_at_atosorigin.com> 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_at_gmail.com> 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_at_gmail.com]
> *Sent:* woensdag 31 januari 2007 10:09
> *To:* oracle-l_at_freelists.org
> *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_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