oracle中谓词带OR语句优化
【背景】
根据研发提供的慢SQL,分析Oracle AWR中SQL,并没有发现相同的SQL.发现类似SQL,只是谓词条件不一样,咨询研发得知,前端根据登录人的角色不同,SQL写法也会变化,通常优化28原则,虽然这个功能用的少,但影响用户体验。
经常会听说,怎么前端传值不一样或者不同用户访问,性能差别很大。本次这个SQL,也是类似情况。逻辑如下,总部人员登录直接赋值总部代码即可,就当前登录人若是分部,需要查找分部下面的人,若分部下面还有分部,也把下面的分部对应的人,查找到(最多2层关系)。(备注Oracle 11.2.0.4)
【具体SQL】
备注:生产上用的最多是mvOrg.CODE等于那个值,加个or后SQL比不加or慢几十倍且性能差.
SELECT *
FROM (SELECT TMP.*, ROWNUM ROW_ID
FROM (SELECT DISTINCT E.ID,
E.CODE,
E.NAME,
E.mobile,
mvOrg.CODE AS orgCode,
mvOrg.NAME AS orgName
FROM XIAOXU.T_TEST_EMPLOYEE E
JOIN XIAOXU.T_TEST_USER U
ON U.USER_ID = E.ID
JOIN XIAOXU.T_TEST_USER_ORG G
ON G.USER_ID = U.USER_ID
JOIN XIAOXU.MV_TEST_ORG_ORDER mvOrg
ON mvOrg.ID = G.ORG_ID
where 1 = 1
and (mvOrg.CODE in
(SELECT code
from XIAOXU.T_TEST_ORG
where PARENT_ID in
(SELECT id
from XIAOXU.T_TEST_ORG
where code = '120168')) or
mvOrg.CODE = '120168')
and G.STATUS = 'VALID'
and E.in_service = 'ACTIVE') TMP
WHERE ROWNUM <= 10)
WHERE ROW_ID > 0
执行时间如下:
Elapsed: 00:00:05.53
【执行计划】
存在问题:
1、返回10条记录消耗140万buffer gets
2、先进行hash join,得到37万数据,进行filter,然后判断是否满足filter(("MVORG"."CODE"='120168' OR IS NOT NULL)),filter性能特别差,通常来说filter效率不高(NL特例)--需要进行优化,消除FILTER。
3、filter访问被驱动表,被驱动表执行27次(13->NESTED LOOPS)
【分析SQL】
1、分页返回TOP 10记录,这个分页框架写法是正确,其实2层就够,这个写法没有啥问题,主要为了兼容下一个页语法。
2、执行计划产生FILTER,导致执行效率低,通常来说FILTER执行效率低。一般可以通过hint或者改写来消除FILTER从而提升效率。12C中对简单OR会进行改写。但低版本中通常需要改写或强制hint。那么如何消除FILER,通过改写SQL或者HINT方式,本次通过改写SQL。
【改写逻辑】
1、通过OR改写是通过union all +LNNVL函数去重.
2、本次案例中是mvOrg.CODE in ( xx OR xx),oracle中in和exists半连接本身就自动去重功能,所以第一种改写使用union all方式.
【改写SQL】
(mvOrg.CODE in
(SELECT code
from XIAOXU.T_TEST_ORG
where PARENT_ID in
(SELECT id
from XIAOXU.T_TEST_ORG
where code = '120168')) or
mvOrg.CODE = '120168')
改写成如下格式:
(mvOrg.CODE in
(SELECT code
from XIAOXU.T_TEST_ORG
where PARENT_ID in
(SELECT id
from XIAOXU.T_TEST_ORG
where code = '120168') union all
select '120168' from dual))
SELECT *
FROM (SELECT TMP.*, ROWNUM ROW_ID
FROM (SELECT DISTINCT E.ID ,
E.CODE,
E.NAME ,
E.mobile ,
E.in_service ,
mvOrg.CODE,
mvOrg.NAME
FROM XIAOXU.T_TEST_EMPLOYEE E
JOIN XIAOXU.T_TEST_USER U
ON U.USER_ID = E.ID
JOIN XIAOXU.T_TEST_USER_ORG G
ON G.USER_ID = U.USER_ID
JOIN XIAOXU.MV_TEST_ORG_ORDER mvOrg
ON mvOrg.ID = G.ORG_ID
where 1 = 1
and (mvOrg.CODE in
(SELECT code
from XIAOXU.T_TEST_ORG
where PARENT_ID in
(SELECT id
from XIAOXU.T_TEST_ORG
where code = '120168') union all
select '120168' from dual))
and G.STATUS = 'VALID'
and E.in_service = 'ACTIVE') TMP
WHERE ROWNUM <= 10)
WHERE ROW_ID > 0
【执行时间】
Elapsed: 00:00:00.04
【改写后执行计划】
1、执行计划由filter变成NL方式。
2、buffer gets从140万降低到442返回10条记录.不管从时间还是资源消耗来讲,提升N个数据量级别。
3、执行时间从5s变成0.04s
【特殊改写方式】
1、由于部门是树形结构,可以通过connect by来实现,经过了解本次分部这个层级最多是2层。如果存在更多层,那么与实际逻辑不一样。经过测试效率与上面union all保持一致。
mvOrg.CODE in
(SELECT code
from XIAOXU.T_TEST_ORG
start with code = '120168'
connect by prior ID=PARENT_ID
)
【总结】
1、or通常使用union all +LNNVL来消除filter执行计划,本次案例是in里面语句,所以无需去重,因为in与exists具备消除重复功能。
2、本次or前面具备树形层级关系,所以可以通过connect by来改写,必须树形结构层级,因为递归检索的,只适合特定场景,否则改写就不等价。
相关文章
- 均衡Oracle 负载均衡实现最佳性能(oracle负载)
- 优化Oracle中索引字段长度的最佳实践(oracle索引字段长度)
- 使用Oracle的IF语句进行条件控制(oracle的if语句)
- Oracle数据库触发器种类简介(oracle触发器类型)
- 函数Oracle窗口函数—为数据分析注入新的动力(oracle窗口)
- Oracle数据库开启归档模式:架构优势突显(oracle数据库开归档)
- 创建Oracle用户:一步一步步骤指南(oracle新建用户名)
- Oracle数据库:实现数据资源高效管理的优势 (oracle数据库的优势)
- 如何优化Oracle子查询的性能?(oracle子查询性能)
- 优化Oracle数据更新性能的技术分享(oracle 更新数据慢)
- Oracle 中 OR 优化的几种方法(oracle中or效率)
- 探索Oracle的内连接之旅(oracle内关联语句)
- Oracle公司凭借张月强实现突飞猛进(oracle公司张月强)
- Oracle数据日志实现机制研究(oracle写数据日志)
- ATM机转账 Oracle 大大提高资金流动效率(ATM机转账oracle)
- EF如何支持Oracle数据库(ef支持oracle吗)
- Oracle主机名解读英文之秘密(oracle主机名的英文)
- 如何用Oracle实现行转列的SQL语句(oracle中行转列语法)
- 优化Oracle事务量实现提速(oracle 事务量)
- Oracle主键 实现数据安全的覆盖性保护(oracle主键覆盖)
- Oracle数据库中主键外键语句使用技巧(oracle主键外键语句)
- Oracle数据库性能优化的规范性实践(oracle优化规范)
- Oracle优化慢查询可促进系统效率(oracle优化慢查询)
- Oracle中解释语句的添加与使用(oracle中注释用什么)
- Oracle数据库精准拼音翻译指南(oracle中文翻译拼音)
- SQL在Oracle中如何测试SQL语句(oracle 中怎么测试)
- 深入Oracle数据库加一个月份(oracle中加月份)
- Oracle取消两个值(oracle 两个值取消)
- 忘记Oracle一体机密码,如何找回(oracle一体机密码)
- Oracle OCM让优化变得更容易(oracle ocm工具)
- Oracle MGS产品实现安全可靠的信息处理(oracle MGS产品)