RE: a explain plan question
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <exriscer@xxxxxxxxx>
- Date: Wed, 31 Jan 2007 10:58:22 +0100
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
---------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
