废话不多说,直接上例子感受一下:
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小,性能占优。