在项目中做权限控制时,需要用组织阶层来控制能够访问的数据,
比如A组织的人可以看到其下属组织的人员数据,或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。
根据需求需要构筑DB的表结构,如下(ORG_RANK)
组织ID(PK) |
上位组织ID |
ORG_ID |
HIGH_ORG_ID |
根据上面的结构,使用Oracle的树查询语句(start with和connect by)来创建SQL语句,如下:
查询指定组织的直属下层组织:
- select ORANK.ORG_ID
- from ORG_RANK ORANK
- where (level - 1) = 1
- start with ORANK.ORG_ID = #orgId#
- connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID
对以上SQL做性能评定时发现出现严重性能问题,(10层组织,3000条数据时)查询时间1分多钟,下面进行了优化。
1、分析执行计划,发现有Full Table,说明使用索引失败,优化的方法是对HIGH_ORG_ID加上索引。
2、虽然只是查询直属下层的组织,但是上面SQL实际执行时,先查询出指定组织的所有下层组织,
然后再从结果里过滤出直属下层的组织(where (level - 1) = 1)。
上面的分析可以得到证明,因为输入倒数第二层组织的执行时间会比输入最上层组织的执行时间少的多。
优化方法是增加connect by语句的条件(and (level - 1) <= 1),不满足条件的子树不会被查询,会省去很多没用的递归查询。
- select ORANK.ORG_ID
- from ORG_RANK ORANK
- where (level - 1) = 1
- start with ORANK.ORG_ID = #orgId#
- connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID
- and (level - 1) <= 1
判断组织A是组织B的上层组织:
方法一:查询出A的所有下层组织,看其中是否有B;
方法二:查询出B的所有上层组织,看其中是否有A。
只要你头脑里自己描绘出一个树型的组织结构,那么你自然会想到方法二的执行速度会明显比方法一块,
方法二是逆行查询,查到的数据量小。
分享到:
相关推荐
Oracle start with.connect by prior子句实现递归查询
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
oracle数据库startwith用法
ORACLE SQL性能优化系列 ORACLE SQL性能优化系列 ORACLE SQL性能优化系列
Oracle企业DBA性能优化 Oracle企业DBA性能优化 Oracle企业DBA性能优化 Oracle企业DBA性能优化
oracle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdf
oracle中的数查询,介绍的详细,有例子。
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
非常经典的oracle11g性能优化书籍,适合深入学习oracle的人员
本文章详细介绍了Oracle中connect by...start with...的用法。
Oracle_SQL性能优化.
Oracle数据库SQL性能优化学习可以用到的。
oracle 10g 性能优化与调整 oracle 10g 性能优化与调整 oracle 10g 性能优化与调整 oracle 10g 性能优化与调整
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
浅析Oracle数据库的性能优化.pdf
ORACLE查询树型关系(connect_by_prior_start_with)
Oracle+SQL性能优化40条.docx
oracle 11g数据库性能优化扫描版。目录: 第一章 优化表性能 第二章 选择和优化索引 第三章 优化实例内存 第四章 监控系统性能 第五章 最小化系统资源争夺 第六章 分析操作系统性能 第七章 检修数据库 第八章 创建...
ORACLE_SQL性能优化大全.pdf