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来改写，必须树形结构层级，因为递归检索的，只适合特定场景，否则改写就不等价。