Re: Wrapping Query in "select *" changes execution path

  • From: Timo Raitalaakso <rafu@xxxxxx>
  • To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 06 Jun 2010 22:49:35 +0300

kyle Hailey wrote:
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 */

I have used the materialize approach with great success. Together with Tane Poder planviz been able to fiqure out several query performance problems.

with ta as (
select /*+materialize*/
< the original query >
 ) select /*+gather_plan_statistics*/ count(*) from ta
;

select *
  from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

http://tech.e2sn.com/apps/planviz

The begining and the end of the plan are replacing the fetching.

-----------------------------------------
|   1 |  TEMP TABLE TRANSFORMATION      |
|   2 |   LOAD AS SELECT                |
...
|  29 |   SORT AGGREGATE                |
|  30 |    VIEW                         |
|  31 |     TABLE ACCESS FULL           |
-----------------------------------------

Of course the count part may be replaced with your paging query if you are interested in seeing some actual rows of the result.

--
Rafu
http://rafudb.blogspot.com/
--
//www.freelists.org/webpage/oracle-l


Other related posts: