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