手机版
你好,游客 登录 注册
背景:
阅读新闻

Oracle递归查询的原理

[日期:2014-06-23] 来源:Linux社区  作者:Linux [字体: ]

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 函数中游标及递归的应用 http://www.linuxidc.com/Linux/2014-06/103553.htm

Oracle递归函数 http://www.linuxidc.com/Linux/2014-06/102687.htm

Oracle 递归查询 http://www.linuxidc.com/Linux/2013-06/85879.htm

Oracle递归START WITH...CONNECT BY PRIOR子句用法 http://www.linuxidc.com/Linux/2013-01/78585.htm

Oracle 使用递归的性能提示 http://www.linuxidc.com/Linux/2013-01/78541.htm

Oracle递归查询(start with) http://www.linuxidc.com/Linux/2012-11/74736.htm
 
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)

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2014-06/103572.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款