Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Pre materialize parts of complex query (Read 5509 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Pre materialize parts of complex query
25.10.07 at 22:55:41
 
Pre materialize parts of complex query
 
Parts of a complex query can be pre-materialized using the WITH clause and the /*+ materialize */ optimizer hint.  
 
When creating complex queries developers often use inline views. As an illustration consider the following query where an inline view avg is created to get the average salary per department.
 
select emp.ename, emp.sal, avg.average_sal
from   scott.emp
,      (        select avg(sal) average_sal
               , deptno  
               from   scott.emp
               group by deptno
      ) avg
where  emp.deptno = avg.deptno
 
When queries get more complex and more inline views are added a  point can be reached where the CBO (Cost Based Optimizer) struggles to find a good execution path. Pre materializing the inline views can be a way of solving this problem. In effect Oracle is creating temporary tables first for the inline views that are going to be used. Then a full table scan on these temporary tables are used instead of a more complex execution plan to retrieve the rows for the view.
 
This can be achieved using the WITH clause where part of the query is defined as a temporary table. In my example below I have defined the avg temporary table in the WITH clause.
 
with
  avg as ( select avg(sal) average_sal
               , deptno  
               from   scott.emp
               group by deptno )
select emp.ename, emp.sal, avg.average_sal
from   scott.emp
,      avg
where  emp.deptno = avg.deptno
 
To materialize the results of the defined temporary tables you can use the /*+ materialize */ undocumented optimizer hint. The statement becomes:
 
with
  avg as ( select /*+ materialize */ avg(sal) average_sal
               , deptno  
               from   scott.emp
               group by deptno )
select emp.ename, emp.sal, avg.average_sal
from   scott.emp
,      avg
where  emp.deptno = avg.deptno
 
Note the change in execution plan.
 
Without the materialize hint:
 
SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=4      Cardinality=14      Bytes=546
 NESTED LOOPS                  Cost=4      Cardinality=14      Bytes=546
  VIEW      Object owner=FEAGLI            Cost=3      Cardinality=3      Bytes=78
   SORT GROUP BY                  Cost=3      Cardinality=3      Bytes=21
    TABLE ACCESS FULL      Object owner=SCOTT      Object name=EMP      Cost=1      Cardinality=14      Bytes=98
  TABLE ACCESS FULL      Object owner=SCOTT      Object name=EMP      Cost=1      Cardinality=5      Bytes=65
  
With the materialize hint:  
  
SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=5      Cardinality=14      Bytes=546
 TEMP TABLE TRANSFORMATION                              
  LOAD AS SELECT                              
   SORT GROUP BY                  Cost=3      Cardinality=3      Bytes=21
    TABLE ACCESS FULL      Object owner=SCOTT      Object name=EMP      Cost=1      Cardinality=14      Bytes=98
  NESTED LOOPS                  Cost=2      Cardinality=14      Bytes=546
   VIEW      Object owner=FEAGLI            Cost=1      Cardinality=3      Bytes=78
    TABLE ACCESS FULL      Object owner=SYS      Object name=SYS_TEMP_0FD9D6654_1A2205A      Cost=1      Cardinality=3      Bytes=21
   TABLE ACCESS FULL      Object owner=SCOTT      Object name=EMP      Cost=1      Cardinality=5      Bytes=65
  
 
See also:
Oracle subquery - With syntax (Subquery factoring clause)
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1142498392
Back to top
 
« Last Edit: 03.11.07 at 21:44:37 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1