文档章节

oracle树形查询 start with connect by

Dylan1009
 Dylan1009
发布于 2017/04/12 09:03
字数 972
阅读 20
收藏 1

 

一、简介
  在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、构造数据

-- 表结构
create table menu(
 id varchar2(64) not null,
 parent_id varchar2(64) not null,
 name varchar2(100) not null,
 depth number(2) not null,
 primary key (id)
)

-- 初始化数据
-- 顶级菜单
insert into menu values ('100000', '0', '顶级菜单1', 1);
insert into menu values ('200000', '0', '顶级菜单2', 1);
insert into menu values ('300000', '0', '顶级菜单3', 1); 

-- 父级菜单
-- 顶级菜单1 直接子菜单
insert into menu values ('110000', '100000', '菜单11', 2);
insert into menu values ('120000', '100000', '菜单12', 2);
insert into menu values ('130000', '100000', '菜单13', 2);
insert into menu values ('140000', '100000', '菜单14', 2); 
-- 顶级菜单2 直接子菜单
insert into menu values ('210000', '200000', '菜单21', 2);
insert into menu values ('220000', '200000', '菜单22', 2);
insert into menu values ('230000', '200000', '菜单23', 2); 
-- 顶级菜单3 直接子菜单
insert into menu values ('310000', '300000', '菜单31', 2); 

-- 菜单13 直接子菜单
insert into menu values ('131000', '130000', '菜单131', 3);
insert into menu values ('132000', '130000', '菜单132', 3);
insert into menu values ('133000', '130000', '菜单133', 3);

-- 菜单132 直接子菜单
insert into menu values ('132100', '132000', '菜单1321', 4);
insert into menu values ('132200', '132000', '菜单1332', 4);

 生成的菜单层次结构如下:
顶级菜单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):
---------------------------------------------------
- access("ID"=PRIOR "PARENT_ID")
- access("ID"='132100')
- access("ID"=PRIOR "PARENT_ID")
 
Note
-----
   - dynamic sampling used for this statement

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

本文转载自:http://www.cnblogs.com/always-online/archive/2015/10/30/4923532.html

Dylan1009
粉丝 5
博文 195
码字总数 131086
作品 0
广州
程序员
私信 提问
oracle 层次查询语句

Oracle中层次查询语法: select ... [level | connect_by_root |connect_by_isleaf |connect_by_iscycle ] from table_name [where] connect by { prior col1=col2 | col1=prior col2 } [sta......

天黑顺路
2017/10/24
0
0
oracle 层次查询

1 定义: 层次查询使用树的遍历,走遍含树形结构的数据集合,来获取树的层次关系报表的方法 树形结构的父子关系,你可以控制: ① 遍历树的方向,是自上而下,还是自下而上 ② 确定层次的开始...

长平狐
2012/09/19
141
0
sql递归查询替代方法

自己在做项目时遇到个问题,求助大家,谢谢。数据库用的是oracle,test表结构如下,id,pid,name,rwno(排列序号),想做个树形菜单, 1)select t.*, level, connect_by_isleaf from tes...

小山羊
2012/11/06
690
1
PostgreSQL Oracle 兼容性 - connect by 2

标签 PostgreSQL , Oracle , 树形查询 , 递归 , connect by , tablefunc , connectby 背景 Oracle connect by语法经常用于有树形关系的记录查询,PostgreSQL使用CTE递归语法,可以实现同样的...

德哥
2018/10/05
0
0
Oracle中start with...connect by子句的用法

Oracle中start with...connect by子句的用法Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是:select … from tablenamestart with 条件1conn...

什么是程序员
2016/11/11
17
0

没有更多内容

加载失败,请刷新页面

加载更多

MySQL左连接问题,右表做筛选,左表列依然在

两张表,一张user表,一张user_log表 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFA......

bengozhong
26分钟前
5
0
重新开始学Java——多线程基础

多线程 进程 主流计算机操作系统都支持同时运行多个任务 , 每个任务通常就是一个程序 , 每个运行中的程序就是一个进程或者多个进程 。 进程的特点 独立性 进程是系统中独立存在的实体 可以...

大家都是低调来的
27分钟前
7
0
注解在Java中是如何工作的?

> 来一点咖啡,准备好进入注解的世界。 注解一直是 Java 的一个非常重要的部分,它从 J2SE 5.0 开始就已经存在了。在我们的应用程序代码中,经常看到 @Override 和 @Deprecated 这样的注解。...

liululee
30分钟前
18
0
Docker 容器连接

Docker 容器连接 容器间的链接有两种方法,你选择其一即可 网络端口映射 docker run -d -P docker run -d -p-P :是容器内部端口随机映射到主机的高端口。-p : 是容器内部端口绑定到指定...

测者陈磊
33分钟前
9
0
车载导航应用中基于Sketch UI主题定制方案的实现

1.导读 关于应用的主题定制,相信大家或多或少都有接触,基本上,实现思路可以分为两类: 内置主题(应用内自定义style) 外部加载方式(资源apk形式、压缩资源、插件等) 其实,针对不同的主题...

阿里云官方博客
38分钟前
15
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部