Oracle 11g的递归with语句

废话不多说，直接上例子感受一下：

with emps (employee_id, name, job_id, salary, lvl) as (
  select employee_id, first_name || ', '|| last_name name, job_id, salary, 1 as lvl
  from employees
  where manager_id is null
  union all
  select emp.employee_id, emp.first_name || ', '|| emp.last_name, emp.job_id, emp.salary, root.lvl + 1
  from employees emp, emps root
  where emp.manager_id=root.employee_id
)
select * from emps;

该SQL的tkprof报告中的执行计划部分：

Rows     Row Source Operation
-------  ---------------------------------------------------
    108  VIEW  (cr=22 pr=0 pw=0 time=0 us cost=7 size=497 card=7)
    108   UNION ALL (RECURSIVE WITH) BREADTH FIRST (cr=22 pr=0 pw=0 time=0 us)
      1    TABLE ACCESS FULL EMPLOYEES (cr=8 pr=0 pw=0 time=0 us cost=3 size=36 card=1)
    107    NESTED LOOPS  (cr=14 pr=0 pw=0 time=11232 us)
    107     NESTED LOOPS  (cr=5 pr=0 pw=0 time=5061 us cost=4 size=372 card=6)
    108      RECURSIVE WITH PUMP  (cr=0 pr=0 pw=0 time=728 us)
    107      INDEX RANGE SCAN EMP_MANAGER_IX (cr=5 pr=0 pw=0 time=670 us cost=0 size=0 card=6)(object id 73958)
    107     TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=9 pr=0 pw=0 time=0 us cost=1 size=216 card=6)

这个树状查询，如果不用递归，可以用connect_by语句实现：

select emp.employee_id, emp.first_name || ', '|| emp.last_name name, emp.job_id, emp.salary, level
from employees emp
start with manager_id is null
connect by prior employee_id=manager_id;

Rows     Row Source Operation
-------  ---------------------------------------------------
    108  CONNECT BY NO FILTERING WITH START-WITH (cr=7 pr=0 pw=0 time=0 us)
    108   TABLE ACCESS FULL EMPLOYEES (cr=7 pr=0 pw=0 time=749 us cost=3 size=3852 card=107)

Connect by 不是ANSI SQL， 递归With是。Connect by的逻辑读(cr)比递归with小，性能占优。