开发者社区> 烟火_> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

oracle树形查询 start with connect by

简介: 一、简介   在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件,prior 关键字表示父数据,prior 条件表示子数据需要满足父数据的什么条件。
+关注继续查看

一、简介
  在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

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

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1894 0
+关注
烟火_
从事java开发多年
文章
问答
文章排行榜
最热
最新
相关电子书
更多
ORACLE 10g 数据库体系结构图
立即下载
Oracle 至PostgreSQL案例分享
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载