Oracle递归查询的原理

程序员文章站 2022-05-13 20:54:09

...

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

相关标签： Oracle递归查询 Oracle递归查询的原理数据库 O

上一篇：有哪些经常被误用的HTML、JavaScript、CSS 的元素、方法和属性？

下一篇：访问属性和方法

Oracle递归查询的原理

mysql仿oracle的decode效果查询

SQL临时表递归查询子信息并返回记录的代码

Sql学习第三天——SQL 关于CTE(公用表达式)的递归查询使用

oracle,mysql,SqlServer三种数据库的分页查询的实例

在oracle 数据库查询的select 查询字段中关联其他表的方法

oracle 查询表名以及表的列名

Oracle中查询本月星期5的所有日期列表的语句

如何查询占CPU高的oracle进程

ORACLE查询表最近更改数据的方法

Oracle跨数据库查询并插入实现原理及代码