Oracle 11g R2 RSF (Recurive Subquery factoring)

Before Oracle 11gR2, we usually use start with and connect by to recursively display the tree structure. See the following script.

Select lpad ('', 2 * level-1,'') | ename, empno, mgr
From emp
Where empno is not null
Connect by prior empno = mgr
Start with mgr is null

ENAME EMPNO MGR
----------------------------------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
 
RSF can be used for implementation starting from 11gR2
 
With sun (lvl, ename, empno, mgr)
(Select 1, lpad ('', 2*1-1,'') | ename, empno, mgr from emp where mgr is null
Union all
Select sun. lvl + 1, lpad ('', 2 * lvl-1,'') | d. ename, d. empno, d. mgr
From sun, emp d
Where sun. empno = d. mgr)
Select lvl, ename, empno, mgr from sun

LVL ENAME EMPNO MGR
--------------------------------------------------
1 KING 7839
2 JONES 7566 7839
2 BLAKE 7698 7839
2 CLARK 7782 7839
3 ALLEN 7499 7698
3 WARD 7521 7698
3 MARTIN 7654 7698
3 SCOTT 7788 7566
3 TURNER 7844 7698
3, JAMES 7900 7698
3 FORD 7902 7566
3 MILLER 7934 7782
4 SMITH 7369 7902
4. ADAMS 7876 7788
 

Observe the above two outputs, with the traditional start with and connect by, and the result order is similar to the preorder traversal of the emp table. RSF, level traversal)