Oracle 11g的递归with语句

来源：互联网发布：曹睿是谁的孩子知乎编辑：程序博客网时间：2024/06/08 04:20

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

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, levelfrom employees empstart with manager_id is nullconnect 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小，性能占优。

1 0