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)