Oracle查询优化-分层查询（树形查询）

1. 简单的树形查询

表结构和数据如下：

--建表语句
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC    VARCHAR2(13)
);
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
create table EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
alter table EMP add constraint PK_EMP primary key (EMPNO);
comment on table emp  is  '员工表';
comment on column emp.empno is  '员工号';
comment on column emp.ename is  '员工姓名';
comment on column emp.job is  '职务';
comment on column emp.mgr is  '上级编号';
comment on column emp.hiredate is  '受雇日期';
comment on column emp.sal is  '薪金';
comment on column emp.comm is  '奖金';
comment on column emp.deptno is  '部门编号';
comment on table dept  is  '部门表';
comment on column dept.deptno is  '部门编号';
comment on column dept.dname is  '部门名称';
comment on column dept.loc is  '部门地址';
--初始化数据

insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;

我们经常会用表来保存上下级关系，如地区表，员工表，组织机构表等，为了按上下级关系递归查询这些数据，就需要用到树形查询，例如：

select level,empno,ename,mgr,(prior ename) as mgr_name 
from emp
start with mgr is null
connect by (prior empno)=mgr;

查询结果如下：

用图来表示如下：

这个语句已“mgr is null”为起点向下循环用“(prior empno)=mgr”递归查询。
Level是一个伪列，表示节点在树中的层级，prior关键字可以返回上级的信息。

2. 根节点、分支节点、叶子节点

connect_by_isleaf是一个伪列，如果当前节点下没有其它的节点，则connect_by_isleaf返回1，否则返回0.

select lpad(' ',(level-1)*2,' ') || empno as empno,
       ename,mgr,level,
       decode(level,1,1) as root_node,--根节点
       case when
           (connect_by_isleaf = 0 and level > 1) then
           1
       end as branch_node, --分支节点
       decode(connect_by_isleaf,1,1) as leaf_node--叶子节点
from emp
start with empno='7566'
connect by (prior empno) = mgr;

--sys_connect_by_path展示全路径
select empno,ename,mgr,sys_connect_by_path(ename,',') as connect_path
from emp
start with empno='7566'
connect by (prior empno)=mgr;

--用sys_connect_by_path合并字符串
with x1 as 
/*1. 分组生成序号 rn*/
     (select deptno,ename,row_number() over(partition by deptno order by ename)as rn
     from emp)
/*2. 用sys_connect_by_path合并字符串*/
select deptno,sys_connect_by_path(ename,',') as emps
from x1
where connect_by_isleaf=1
start with rn=1
connect by (prior deptno)=deptno
        and (prior rn)=rn-1; 
--用listagg的方式（不需要生成rn）            
with x1 as 
     (select deptno,ename
     from emp)select deptno,listagg(ename,',') within group(order by ename) as enames from x1 group by deptno;

3.树形查询中的排序

一般排序的方式
select lpad(’ ‘,(level-1)*2,’ ') || empno as empno,ename,mgr
from emp
start with empno=‘7566’
connect by (prior empno)=mgr;
order by empno desc;
这种排序方式无法再看清上下级关系，失去了树形查询的意义，我们应该使用树形查询的专用关键字“SIBLINGS”

树形查询中的排序

select lpad(' ',(level-1)*2,' ') || empno as empno,ename,mgr
from emp
start with empno='7566'
connect by (prior empno)=mgr
order SIBLINGS by emp.empno desc;

可以看到，这个语句只对同一分支（7566）下的（7902，,7788）进行排序，而没有影响到树形结构。
需要注意的是SIBLINGS关键字仅对树形查询生效，所以这里要用emp.empno，如果不加emp前缀，则指的是表达式的结果，会报错。

4.树形查询中的where

传统where的使用方式

select empno,mgr,ename,deptno
from emp
where deptno='20'
start with mgr is null
connect by (prior empno)=mgr;

这个结果明显不对，因为部门20不存在mgr为空的数据，那么也就不该返回数据。上面查询可等价改写为：

select * from (select empno,mgr,ename,deptno
from emp
start with mgr is null
connect by (prior empno)=mgr)
where deptno='20';

也就是说，上面的语句实际是先上树，再过滤。

树形查询中的where
而只查询部门20的数据，且部门20的起始条件为empno=‘7566’，所以应该这样写：

select empno,mgr,ename,deptno
from (select * from emp where deptno='20')
start with empno ='7566'
connect by (prior empno)=mgr;

本文内容来自《Oracle查询优化改写技巧与案例2.0》/有教无类，落落著。北京：电子工业出版社，2018.6