Oracle递归查询的原理

以start with ename =

在Oracle 10g下，来到scott用户下，分别以层次 1,2,3,4上的节点做实验：
 
当start with是根节点(level=1)，要查其子节点，connect by pump和emp都是被扫描4次(总的层次)。
 
当start with是根节点(level=2)，要查其子节点，，connect by pump和emp被扫描3次。
 
当start with是根节点(level=3)，要查其子节点，connect by pump和emp被扫描2次。

当start with是根节点(level=4)，要查其子节点，connect by pump和emp被扫描1次。

注意的是：leve=2,level=3不是叶子节点，如果是叶子节点，那connect by pump和emp只扫描一次。
 
  Operation            Name    Starts
 
  FILTER             
    TABLE ACCESS FULL    EMP        1
  HASH JOIN         
    CONNECT BY PUMP                  4
    TABLE ACCESS FULL    EMP        4

我来解读上面的执行计划，以start with ename = 'KING'为例，显示对EMP通过"ENAME"='KING'过滤找到节点作为根节点（集合A），通过集合A到下一级所有满足条件的节点(集合B)，通过集合B再到下一级所有满足条件的节点(集合C)，树有几级就CONNECT BY PUMP几次。

Oracle 函数中游标及递归的应用

Oracle递归函数

Oracle 递归查询

Oracle递归START WITH...CONNECT BY PRIOR子句用法

Oracle 使用递归的性能提示

Oracle递归查询(start with)
 
SQL> set pagesize 100
 SQL> --根节点 level=1
 SQL> select e.empno, e.ename, e.mgr, e.deptno,level
      from emp e
      start with ename = 'KING'
    connect by prior empno = mgr;
 
    EMPNO ENAME            MGR    DEPTNO      LEVEL
 ---------- ---------- ---------- ---------- ----------
      7839 KING                          10          1
      7566 JONES            7839        20          2
      7788 SCOTT            7566        20          3
      7876 ADAMS            7788        20          4
      7902 FORD            7566        20          3
      7369 SMITH            7902        20          4
      7698 BLAKE            7839        30          2
      7499 ALLEN            7698        30          3
      7521 WARD            7698        30          3
      7654 MARTIN          7698        30          3
      7844 TURNER          7698        30          3
      7900 JAMES            7698        30          3
      7782 CLARK            7839        10          2
      7934 MILLER          7782        10          3
 已选择14行。
 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 PLAN_TABLE_OUTPUT
 -----------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------------------
 SQL_ID  6as71p9t5arg3, child number 0
 -------------------------------------
 select e.empno, e.ename, e.mgr, e.deptno,level  from emp e  start with ename = 'KING' connect by prior empno
 = mgr
 Plan hash value: 3364448299
 -----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  OMem |  1Mem | Used-Mem |
 -----------------------------------------------------------------------------------------------------------------------
 |*  1 |  CONNECT BY WITH FILTERING|      |      1 |        |    14 |00:00:00.01 |      35 |  9216 |  9216 | 8192  (0)|
 |*  2 |  FILTER                  |      |      1 |        |      1 |00:00:00.01 |      7 |      |    |            |
 |  3 |    TABLE ACCESS FULL      | EMP  |      1 |    14 |    14 |00:00:00.01 |      7 |      |    |            |
 |*  4 |  HASH JOIN              |      |      4 |        |    13 |00:00:00.01 |      28 |  1036K|  1036K|  776K (0)|
 |  5 |    CONNECT BY PUMP        |      |      4 |        |    14 |00:00:00.01 |      0 |      |    |            |
 |  6 |    TABLE ACCESS FULL      | EMP  |      4 |    14 |    56 |00:00:00.01 |      28 |      |    |            |
 |  7 |  TABLE ACCESS FULL      | EMP  |      0 |    14 |      0 |00:00:00.01 |      0 |      |    |            |
 -----------------------------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("ENAME"='KING')
    2 - filter("ENAME"='KING')
    4 - access("MGR"=NULL)
 
SQL> --level=2
 SQL> select e.empno, e.ename, e.mgr, e.deptno,level
      from emp e
      start with ename = 'JONES'
    connect by prior empno = mgr;
      EMPNO ENAME            MGR    DEPTNO      LEVEL
 ---------- ---------- ---------- ---------- ----------
      7566 JONES            7839        20          1
      7788 SCOTT            7566        20          2
      7876 ADAMS            7788        20          3
      7902 FORD            7566        20          2
      7369 SMITH            7902        20          3
 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 SQL_ID  2bcjwvmbyg7a5, child number 1
 -------------------------------------
 select e.empno, e.ename, e.mgr, e.deptno,level  from emp e  start with ename = 'JONES' connect by prior empno
 = mgr
 Plan hash value: 3364448299
 -----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  OMem |  1Mem | Used-Mem |
 -----------------------------------------------------------------------------------------------------------------------
 |*  1 |  CONNECT BY WITH FILTERING|      |      1 |        |      5 |00:00:00.01 |      28 |  9216 |  9216 | 8192  (0)|
 |*  2 |  FILTER                  |      |      1 |        |      1 |00:00:00.01 |      7 |      |    |            |
 |  3 |    TABLE ACCESS FULL      | EMP  |      1 |    14 |    14 |00:00:00.01 |      7 |      |    |            |
 |*  4 |  HASH JOIN              |      |      3 |        |      4 |00:00:00.01 |      21 |  1036K|  1036K|  404K (0)|
 |  5 |    CONNECT BY PUMP        |      |      3 |        |      5 |00:00:00.01 |      0 |      |    |            |
 |  6 |    TABLE ACCESS FULL      | EMP  |      3 |    14 |    42 |00:00:00.01 |      21 |      |    |            |
 |  7 |  TABLE ACCESS FULL      | EMP  |      0 |    14 |      0 |00:00:00.01 |      0 |      |    |            |
 -----------------------------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("ENAME"='JONES')
    2 - filter("ENAME"='JONES')
    4 - access("MGR"=NULL)
 
SQL> --level=3
 SQL> select e.empno, e.ename, e.mgr, e.deptno,level
      from emp e
      start with ename = 'SCOTT'
    connect by prior empno = mgr;
      EMPNO ENAME            MGR    DEPTNO      LEVEL
 ---------- ---------- ---------- ---------- ----------
      7788 SCOTT            7566        20          1
      7876 ADAMS            7788        20          2
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
PLAN_TABLE_OUTPUT
 -----------------------------------------------------------------------------------------------------------------------
 SQL_ID  fqf7r75c9atqv, child number 0
 -------------------------------------
 select e.empno, e.ename, e.mgr, e.deptno,level  from emp e  start with ename = 'SCOTT' connect by prior empno
 = mgr
 Plan hash value: 3364448299
 -----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  OMem |  1Mem | Used-Mem |
 -----------------------------------------------------------------------------------------------------------------------
 |*  1 |  CONNECT BY WITH FILTERING|      |      1 |        |      2 |00:00:00.01 |      21 |  9216 |  9216 | 8192  (0)|
 |*  2 |  FILTER                  |      |      1 |        |      1 |00:00:00.01 |      7 |      |    |            |
 |  3 |    TABLE ACCESS FULL      | EMP  |      1 |    14 |    14 |00:00:00.01 |      7 |      |    |            |
 |*  4 |  HASH JOIN              |      |      2 |        |      1 |00:00:00.01 |      14 |  1036K|  1036K|  282K (0)|
 |  5 |    CONNECT BY PUMP        |      |      2 |        |      2 |00:00:00.01 |      0 |      |    |            |
 |  6 |    TABLE ACCESS FULL      | EMP  |      2 |    14 |    28 |00:00:00.01 |      14 |      |    |            |
 |  7 |  TABLE ACCESS FULL      | EMP  |      0 |    14 |      0 |00:00:00.01 |      0 |      |    |            |
 -----------------------------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("ENAME"='SCOTT')
    2 - filter("ENAME"='SCOTT')
    4 - access("MGR"=NULL)