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