Oracle递归查询的原理

浏览数：23 / 时间：2015年06月12日

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