Oracle递归查询的原理

最新推荐文章于 2021-11-19 15:14:08 发布
最新推荐文章于 2021-11-19 15:14:08 发布
阅读量3.2k 收藏 2
点赞数
分类专栏： Oracle SQL优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/stevendbaguo/article/details/31343409
版权

    在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)

深圳gg
关注
专栏目录
oracle递归查询性能优化,Oracle性能优化 之 库缓存
weixin_35112750的博客
04-05 460
一、库缓存中的信息1．什么是执行计划2．为什么要共享执行计划执行计划的生成要耗费许多CPU时间，而且优化器会将生成的执行计划存放到共享池中。如果你执行很多相同的语句，但没有共享执行计划，优化器每次都要搜索共享池、查找有没有可以共享的执行计划，没有查找后它自己生成，再把生成的执行计划存入共享池。就是说相同的语句如果你没有共享计划，不但消耗了更多的CPU生成执行计划，而且每次还要搜索共享池、保存新生成...
Oracle中递归查询的用法
最新发布
m0_73549087的博客
08-29 526
递归查询主要用于处理层次结构数据，例如组织的员工结构。在Oracle 11g Release 2及以上版本中，可以使用递归公用表表达式（Recursive Common Table Expressions，简称CTE）来进行层次查询。这通常通过语法实现。
递归函数工作原理
热门推荐
09-29 1万+
递归（recursion）就是子程序（或函数）直接调用自己或通过一系列调用语句间接调用自己，是一种描述问题和解决问题的基本方法。       递归通常用来解决结构自相似的问题。所谓结构自相似，是指构成原问题的子问题与原问题在结构上相似，可以用类似的方法解决。具体地，整个问题的
递归的基本原理
Tomorrow Never Knows
03-09 1万+
看《算法笔记》到递归了，遇到稍稍复杂一点的递归就会昏头。查阅资料发现大神们推崇《The Little Schemer》和《SCIP计算机程序的构造和解释》这两本书，第一本貌似不太厚，但是只有全英版本，目前正在准备一件比较重要的事情，来不及看这两本超级经典了，有时间了真是一定要看啊。 今天中午午休时拿出了《C Primer Plus》，带着一点点希望，想从这里面找到一点“灵感”，之前也特意看过这本...
Oracle 递归运行原理
liuguodong1413的博客
11-24 433
一、准备数据 create table t_test as select rownum id, cast (dbms_random.value*99 as Integer) x from dual connect by rownum7; -- 查看 select * from t_test; - id x 1 1 51
oracle 使用递归的性能提示测试对比
09-10
这里，我们将深入讨论递归查询的工作原理、性能提示以及两种不同实现方式的对比。 首先，让我们理解递归查询的基本语法。在Oracle中，`START WITH` 和 `CONNECT BY` 是用于构建递归查询的关键部分。`START WITH` ...
PostgreSQL树形结构的递归查询示例
09-09
在数据库管理中，处理树形结构的数据是一种常见的需求，例如组织结构、目录树或产品分类等。PostgreSQL 提供了一种...在处理树形结构时，理解递归查询的工作原理至关重要，这有助于构建更高效和灵活的数据查询方案。
Oracle中的树状查询(递归查询)
08-12
1. **递归查询原理** 递归查询基于`CONNECT BY`子句，它允许我们定义一个起始点并根据特定条件进行递归。`PRIOR`关键字用于指定当前行与父行的关系。通过这些工具，Oracle可以构建出一个层次结构，展示出数据的树状...
一种MySQL数据库SQL递归查询的研究与实现.pdf
10-10
因此，通过模拟堆栈的原理，我们可以设计一条SQL语句，利用嵌套的JOIN或子查询来模拟迭代过程，逐步展开层次结构，从而实现递归查询的效果。 实现这样的SQL递归查询，通常需要以下步骤： 1. 初始化：定义起始条件，...
ORACLE WITH递归跨行计算，将上一行计算结果传到下一行
qq_20504081的博客
11-19 1941
将上一行一整行的数据还有计算出的结果一并带到下一行去计算，以实现跨行累计计算的目的。
Oracle Connect by原理
JangBingYang的博客
11-04 927
浅谈Oracle Connect by原理 Connect by在oracle中亦比较常用，在一些需求环境中仍是举足轻重的，特别是在存在父子关系、层级关系情况下遍历的；当然也不仅限于此，其与正则匹配搭配使用可简单实现一些较为复杂的业务需求，如将一串字符串以某个字符分割，以表结果的形式展示。简单来说，其原理可理解为“深度优先遍历”；本文将以一些简单的例子来阐述己之拙见，只阐述带不带起始节点条件的情况下。本文所及例子中，忽略物理存储块的实际数据。 语法： { CONNECT BY [ NOCYCLE
递归原理简析
weixin_42178281的博客
10-16 1157
一.什么是递归？ 递归经典题型 1.递归实现二分查找 public static int binarySearch(int[] list, int key){ if(list == null || list.length == 0){ return -1; } return binaySearchBrRecur(list,...
Oracle 递归查询详解
X-rapido的专栏
01-22 9959
Oracle递归查询详解 , SYS_CONNECT_BY_PATH函数
oracle递归的性能,oracle 使用递归的性能提示测试对比
weixin_39974932的博客
04-09 186
select count(0) cou,max(levels)+1 as levelsselect C.a1,C.a2,C.levels...from(Select A.a1 ,A.a2,,,level levels//层次关系from Awhere A.a1=,,,and A.a2=....start with A.a1 = 12connect by nocylce prior A.ID=A.P...
oracle递归查询性能优化,关于子查询，递归循环的效率问题
weixin_42165490的博客
04-05 707
本帖最后由 gongcheng28 于 2012-4-10 14:48 编辑-- Create tablecreate table master_info(masterid varchar2(32),parent varchar2(32));alter table master_infoadd constraint UK01_master_info unique (ma...
oracle的递归查询语句
vip_ljq的专栏
09-01 477
比如模块表 mod  字段  number modId  number parentModId  varchar modName  从下往上查  select * from mod m start with m.modId = ? connect by prior m.parentModId = m.modId  从上往下查  select * from mod m start with m.pa...
oracle递归查询最浅显的用法
ohaozy的专栏
09-14 309
条件左边的字段为父亲，就是向上查询，左边的字段为孩子就是向下查询。 --向上查询，也就是指定某个节点开始，一直查到根节点，条件为 parent=child SELECT *   FROM CATALOG TCONNECT BY PRIOR T.PARENTID = T.ID  START WITH T.ID = 2224  ORDER BY T.PARENTID, T.ID; --向下查询，也...
Oracle Golden Gate翻译模式详解及编译原理应用
原始模式是Oracle Golden Gate中的，用于处理数值计算，其中N、S、R、B、M和P都是不同的非终结符，代表了不同的语法结构。原始模式的目的是在计算过程中累加和更新节点值。 新的翻译模式引入了M和P两个新的非终结符...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值