Oracle递归查询的原理

754632

   在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几次。
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)

SQL> --level=4
SQL> select e.empno, e.ename, e.mgr, e.deptno,level
      from emp e
     start with ename = 'SMITH'
    connect by prior empno = mgr;
     EMPNO ENAME             MGR     DEPTNO      LEVEL
---------- ---------- ---------- ---------- ----------
      7369 SMITH            7902         20          1

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  f5fvjuk1j8mak, child number 1
-------------------------------------
select e.empno, e.ename, e.mgr, e.deptno,level   from emp e  start with ename = 'SMITH' 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 |        |      1 |00:00:00.01 |      14 |  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               |      |      1 |        |      0 |00:00:00.01 |       7 |  1036K|  1036K|  318K (0)|
|   5 |    CONNECT BY PUMP        |      |      1 |        |      1 |00:00:00.01 |       0 |       |    |             |
|   6 |    TABLE ACCESS FULL      | EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |    |             |
|   7 |   TABLE ACCESS FULL       | EMP  |      0 |     14 |      0 |00:00:00.01 |       0 |       |    |             |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='SMITH')
   2 - filter("ENAME"='SMITH')
   4 - access("MGR"=NULL)