oracle中谓词带OR语句优化

DB说 2020-11-02

2196

【背景】

根据研发提供的慢SQL,分析Oracle AWR中SQL，并没有发现相同的SQL.发现类似SQL,只是谓词条件不一样，咨询研发得知，前端根据登录人的角色不同，SQL写法也会变化，通常优化28原则，虽然这个功能用的少，但影响用户体验。

经常会听说，怎么前端传值不一样或者不同用户访问，性能差别很大。本次这个SQL，也是类似情况。逻辑如下，总部人员登录直接赋值总部代码即可，就当前登录人若是分部，需要查找分部下面的人，若分部下面还有分部，也把下面的分部对应的人，查找到(最多2层关系)。

【具体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

最后修改时间：2020-11-25 18:10:22

文章转载自DB说，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

oracle中谓词带OR语句优化

评论