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