Oracle递归查询的原理

在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.htmOracle递归函数 http://www.linuxidc.com/Linux/2014-06/102687.htmOracle 递归查询 http://www.linuxidc.com/Linux/2013-06/85879.htmOracle递归START WITH...CONNECT BY PRIOR子句用法 http://www.linuxidc.com/Linux/2013-01/78585.htmOracle 使用递归的性能提示 http://www.linuxidc.com/Linux/2013-01/78541.htmOracle递归查询（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本文永久更新链接地址