oracle树形查询 start with connect by

最新推荐文章于 2022-08-12 16:54:48 发布

莱克星顿的枪声

最新推荐文章于 2022-08-12 16:54:48 发布

阅读量211

点赞数

分类专栏：数据库 oracle

数据库同时被 2 个专栏收录

35 篇文章 0 订阅

订阅专栏

oracle

3 篇文章 0 订阅

订阅专栏

原文

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、构造数据

按 Ctrl+C 复制代码

　　生成的菜单层次结构如下：
顶级菜单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, start with connect by, 树形查询

好文要顶关注我收藏该文

烟火_
关注 - 5
粉丝 - 115

+加关注

« 上一篇： mybatis oracle BLOB类型字段保存与读取
» 下一篇： oracle行转列与列转行

	<p class="postfoot">
		posted on <span id="post-date">2015-10-30 16:16</span> <a href="https://www.cnblogs.com/always-online/">烟火_</a> 阅读(<span id="post_view_count">19123</span>) 评论(<span id="post_comment_count">1</span>)  <a href="https://i.cnblogs.com/EditPosts.aspx?postid=4923532" rel="nofollow">编辑</a> <a href="#" onclick="AddToWz(4923532);return false;">收藏</a>
	</p>
</div>
<script type="text/javascript">var allowComments=true,cb_blogId=170201,cb_entryId=4923532,cb_blogApp=currentBlogApp,cb_blogUserGuid='14500b3b-0358-e311-8d02-90b11c0b17d6',cb_entryCreatedDate='2015/10/30 16:16:00';loadViewCount(cb_entryId);var cb_postType=1;</script>

</div>