Oracle 11g R2 RSF(Recurive Subquery factoring)
月亮的技术 2013-01-01
在Oracle 11gR2之前，一般用start with和connect by来递归的显示树形结构，请看如下脚本
select lpad(' ',2*level-1,' ') || ename 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
从11gR2开始，可以用RSF来实现
with sun(lvl,ename,empno,mgr) as
(select 1,lpad(' ',2*1-1,' ') || ename ename,empno,mgr from emp where mgr is null
union all
select sun.lvl+1,lpad(' ',2*lvl-1,' ') || d.ename 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
观察一下上述两份输出，用传统的start with和connect by，结果顺序类似于对emp表的前序遍历（preorder traversal）；而使用RSF，则是对emp表的层次遍历（level traversal）
