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