一、简介
在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。如下
start with id= '10001' connect by prior parent_id= id and prior num = 5
表示查询id为10001，并且递归查询parent_id=id,为5的记录。
二、实例
1、构造数据
1 -- 表结构
2 create table menu(
3 id varchar2(64) not null,
4 parent_id varchar2(64) not null,
5 name varchar2(100) not null,
6 depth number(2) not null,
7 primary key (id)
8 )
9
10 -- 初始化数据
11 -- *菜单
12 insert into menu values ('100000', '0', '*菜单1', 1);
13 insert into menu values ('200000', '0', '*菜单2', 1);
14 insert into menu values ('300000', '0', '*菜单3', 1);
15
16 -- 父级菜单
17 -- *菜单1 直接子菜单
18 insert into menu values ('110000', '100000', '菜单11', 2);
19 insert into menu values ('120000', '100000', '菜单12', 2);
20 insert into menu values ('130000', '100000', '菜单13', 2);
21 insert into menu values ('140000', '100000', '菜单14', 2);
22 -- *菜单2 直接子菜单
23 insert into menu values ('210000', '200000', '菜单21', 2);
24 insert into menu values ('220000', '200000', '菜单22', 2);
25 insert into menu values ('230000', '200000', '菜单23', 2);
26 -- *菜单3 直接子菜单
27 insert into menu values ('310000', '300000', '菜单31', 2);
28
29 -- 菜单13 直接子菜单
30 insert into menu values ('131000', '130000', '菜单131', 3);
31 insert into menu values ('132000', '130000', '菜单132', 3);
32 insert into menu values ('133000', '130000', '菜单133', 3);
33
34 -- 菜单132 直接子菜单
35 insert into menu values ('132100', '132000', '菜单1321', 4);
36 insert into menu values ('132200', '132000', '菜单1332', 4);
37
生成的菜单层次结构如下：
*菜单1
菜单11
菜单12
菜单13
菜单131
菜单132
菜单1321
菜单1322
菜单133
菜单14
*菜单2
菜单21
菜单22
菜单23
*菜单3
菜单31
2、SQL查询
--prior放的左右位置决定了检索是自底向上还是自顶向下. 左边是自上而下（找子节点），右边是自下而上（找父节点）
--找父节点
select * from menu start with id='130000' connect by id = prior parent_id;
--找子节点节点
-- (子节点)id为130000的菜单，以及130000菜单下的所有直接或间接子菜单(prior 在左边， prior、parent_id(等号右边)在右边）
select * from menu start with id='130000' connect by prior id = parent_id ;
-- (父节点)id为1321的菜单，以及1321菜单下的所有直接或间接父菜单(prior、parent_id(等号左边) 都在左边)
select * from menu start with id='132100' connect by prior parent_id = id;
-- prior 后面跟的是(parent_id) 则是查找父节点,prior后面跟的是(id)则是查找子节点
--根据菜单组分类统计每个菜单包含子菜单的个数
select id, max(name) name, count(1) from menu
group by id
connect by prior parent_id = id
order by id
-- 查询所有的叶子节点
select t2.* from menu t2 where id not in(select t.parent_id from menu t) order by id;
三、性能问题
对于 start with connect by语句的执行，oracle会进行递归查询，当数据量大的时候会产生性能相关问题。
--生成执行计划
explain plan for select * from menu start with id='132100' connect by prior parent_id = id; -- 查询执行计划
select * from table( dbms_xplan.display);
语句执行计划结果如下：
Plan hash value: 3563250490 ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | MENU | 1 | 133 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0018586 | 1 | | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | CONNECT BY PUMP | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| MENU | 1 | 133 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0018586 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("ID"=PRIOR "PARENT_ID")
3 - access("ID"='132100')
7 - access("ID"=PRIOR "PARENT_ID") Note
-----
- dynamic sampling used for this statement