Sunday, February 5, 2012

Recursive Subquery Factoring

Here is a 11g R1 hierarchical query:
col text format a40
col mgr format 9999

select rpad(' ',2*(level-1))||empno||': '||ename text,
mgr
  from scott.emp
       connect by prior empno=mgr
       start with job='PRESIDENT'
 order siblings by ename;

TEXT                       MGR
----------------------------- -----
7839: KING
  7698: BLAKE                 7839
    7499: ALLEN               7698
    7900: JAMES               7698
    7654: MARTIN              7698
    7844: TURNER              7698
    7521: WARD                7698
  7782: CLARK                 7839
    7934: MILLER              7782
  7566: JONES                 7839
    7902: FORD                7566
      7369: SMITH             7902
    7788: SCOTT               7566
      7876: ADAMS             7788

Execution Plan
----------------------------------------------------------
Plan hash value: 763482334

-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    14 |   4    (25)|
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |      |
|   2 |   TABLE ACCESS FULL            | EMP  |    14 |   3     (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MGR"=PRIOR "EMPNO")
       filter("JOB"='PRESIDENT')


And here, we see the equivalent query using the 11g R1 with clause recursive subquery factoring syntax:
col text format a40
col mgr format 9999

with empl (empno, ename, xlevel, mgr) as
  (select empno, ename, 1, mgr
from scott.emp
where job='PRESIDENT'
   union all
   select e.empno, e.ename, empl.xlevel+1, e.mgr
from scott.emp e, empl
where e.mgr=empl.empno)
  search depth first by ename set ord
select rpad(' ',2*xlevel)||empno||': '||ename text,
mgr
  from empl;

TEXT                       MGR
------------------------- -----
  7839: KING
    7698: BLAKE            7839
      7499: ALLEN           7698
      7900: JAMES           7698
      7654: MARTIN        7698
      7844: TURNER        7698
      7521: WARD           7698
    7782: CLARK            7839
      7934: MILLER        7782
    7566: JONES            7839
      7902: FORD           7566
    7369: SMITH            7902
      7788: SCOTT           7566
    7876: ADAMS            7788

14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3907725112

-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |  8   (25)|
|   1 |  VIEW                    |      |    25 |    8   (25)|
|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|      |       |            |
|*  3 |    TABLE ACCESS FULL            | EMP  |     3 |    3    (0)|
|*  4 |    HASH JOIN                |      |    22 |    4   (25)|
|   5 |     RECURSIVE WITH PUMP         |      |       |       |
|*  6 |     TABLE ACCESS FULL            | EMP  |    13 |    3    (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='PRESIDENT')
   4 - access("E"."MGR"="EMPL"."EMPNO")
   6 - filter("E"."MGR" IS NOT NULL)


Recursive WITH clause

CONNECT BY is an Oracle oddity. But does recursive with performs as well as connect by?
CONNECT BY Clause
select
   empno,mgr
from
   big_emp
connect by
   mgr = prior empno
start with
   mgr is null;


E M
- -
1 - 
2 1
4 2
6 2
8 6

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

Recursive WITH clause:
with
   e(empno,mgr) as (
     select
       empno,
       mgr
     from
       big_emp
     where
        mgr is null
     union all
     select
        f.empno,
        f.mgr
     from
         big_emp f, e
     where
       e.empno=f.mgr)
select
   empno,
   mgr
from e;

E M
- -
1 - 
2 1
3 1
4 2
5 3
...

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

In this particular simple case, it seems CONNECT BY has a 1% higher cost.

No comments:

Post a Comment