你的位置:首页 > 数据库

[数据库]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( 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 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

  通过该执行计划得知,改语句执行了7步操作,才将结果集查询并返回。当需要查询条件进行过滤的时候,我们可以通过查看执行计划从而对sql进行优化。

 




跟团沙巴旅游需要多少钱几月份去沙巴旅游最好什么时候去沙巴旅游最便宜去沙巴旅游跟团要多少钱去沙巴旅游需要多少钱长隆欢乐世界万圣节活动介绍?广州长隆万圣节是几号到几号? 长隆万圣节订票电话?广州长隆万圣节预订优惠吗? 长隆万圣节时间?广州长隆万圣节有什么好玩的? 长隆万圣节2015门票价格?广州长隆万圣节活动安排? 中秋节去紫莲度假村有什么好玩的?潮州紫莲度假村晚上玩什么? 紫莲森林度假村9月有什么活动?潮州紫莲森林度假村游玩攻略? 紫莲森林度假村首届推广月活动时间?潮州紫莲森林度假村推广月活动攻略? 潮州紫莲度假村网站?紫莲度假村最新活动有哪些? 2015雷公峡漂流开漂了吗?惠州雷公峡漂流开漂时间? 稻城亚丁到底有哪些好玩儿的? 隐贤山庄门票包含水上乐园?东莞隐贤山庄水上游乐世界门票价格? 北京李大钊故居在哪?怎么去? 翡翠凉粉:小小三峡特色小吃(图) 四川燕子沟详细介绍 长江三峡购物特产概况 都江堰的传说--望娘滩 IDT7201LA20SO Datasheet IDT7201LA20SO Datasheet IDT2308B-2DCGI Datasheet IDT2308B-2DCGI Datasheet ICS843002AGI-01LFT Datasheet ICS843002AGI-01LFT Datasheet 欧洲旅游跟团 欧洲旅游跟团 欧洲旅游跟团 跟团去欧洲旅游 跟团去欧洲旅游 跟团去欧洲旅游 欧洲旅游团 欧洲旅游团 欧洲旅游团