RE: Wrapping Query in "select *" changes execution path
- From: "Larry G. Elkins" <elkinsl@xxxxxxxxx>
- To: "'Oracle-L List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Jun 2010 07:53:03 -0500
For execution speed alone how about use of NO_MERGE in the IN-LINE view and a
ROWNUM > 1 in the wrapper.
SELECT * from (select /*+ NO_MERGE */ ..... FROM TABLE ) where rownum > 1
Here's an example trace of such an approach showing the query completes, then
results get thrown out thus nothing fetched:
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT (cr=449 r=265 w=0 time=367894 us)
0 FILTER (cr=449 r=265 w=0 time=367868 us) --<<< ROWNUM>1 throwing
everything away
6392 VIEW (cr=449 r=265 w=0 time=365293 us)
6392 WINDOW SORT (cr=449 r=265 w=0 time=356179 us)
6392 WINDOW SORT (cr=449 r=265 w=0 time=320477 us)
6392 WINDOW SORT (cr=449 r=265 w=0 time=295220 us)
6392 HASH JOIN OUTER (cr=449 r=265 w=0 time=265567 us)
6392 TABLE ACCESS FULL REFRDEL_DELETE_CNT (cr=334 r=154 w=0
time=147566 us)
4019 VIEW (cr=115 r=111 w=0 time=105388 us)
4019 SORT GROUP BY (cr=115 r=111 w=0 time=103924 us)
49313 TABLE ACCESS FULL REFRDEL_DELETE_PROJ_ID (cr=115 r=111 w=0
time=67391 us)
Larry G. Elkins
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of kyle Hailey
Sent: Friday, June 04, 2010 1:58 AM
To: ukja.dion@xxxxxxxxx
Cc: rjfeighery@xxxxxxxxx; Oracle-L List
Subject: Re: Wrapping Query in "select *" changes execution path
I was wondering this myself, as I want to test execution speeds alone and leave
out the data fetching overhead. I was thinking
maybe to wrap the original query in a Common Table Expression, ie the
"with clause" and use the hint /*+ materialize */
though have sat down to test this yet.
Best Wishes
Kyle Hailey
--
//www.freelists.org/webpage/oracle-l
Other related posts:
