Oracle树形查询 start with connect by

一、简介
在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（ 3id varchar2（64） not null, 4parent_id varchar2（64） not null, 5name varchar2（100） not null, 6depth number（2） not null, 7primary 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 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 *fromtable（ 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 通过该执行计划得知，改语句执行了7步操作，才将结果集查询并返回。当需要查询条件进行过滤的时候，我们可以通过查看执行计划从而对sql进行优化。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址