不同列or关联同一列的等价改写

1、问题项目中遇到or关联场景即一张表两个列与另外一张表同一个列关联经常因为一些复杂场景会出现一些性能问题目前根据遇到的情况找到一种可以等价改写方式并且能够适用这一类场景。2、分析2.1、场景1 left join or关联同一张表不同列selectNVL(DM.ID,DS.MID)newMID,DS.* from MM_S DS leftjoinMM_DB DM on(DS.MIDDM.ID orDS.MIDDM.OLD_ID)whereDS.BCODE0402AND DS.acqty0AND DS.uqty0AND DS.USin(1,2);理解DS.MID DM.ID OR DS.MID DM.OLD_ID意思是如果DM.ID没匹配就判断同一行的DM.OLD_ID是否能匹配union all没有去重所以还不能简单直接改成union all接下来进行拆解分析假设DS.MID(1),(3),(3)DM.ID,DM.OLD_ID(1,2),(2,1),(3,3),(4,4)此时DS.MID DM.ID的结果1、(1)(1,2)(1,1,2)2、(3)(3,3)(3,3,3)3、(3)(3,3)(3,3,3)DS.MID DM.OLD_ID的结果1、(1)(2,1)(1,2,1)2、(3)(3,3)(3,3,3)3、(3)(3,3)(3,3,3)union all的结果(1,1,2)(3,3,3)(3,3,3)(1,2,1)(3,3,3)(3,3,3)union all是把同一行的两个列拆成两行了实际上or关联的结果1、(1)(1,2)(1,1,2)1、(1)(2,1)(1,2,1)2、(3)(3,3)(3,3,3) --第二行的DS.MID DM.ID3、(3)(3,3)(3,3,3) – 第三行的DS.MID DM.ID因此我们应该要去重union 有去重的效果。在DM.ID是主键唯一性且DM.OLD_ID如果数据情况如下(1,2),(2,1)拆解成select DM.ID,DM.ID ID1 from MM_DB DMunion select DM.OLD_ID,DM.ID ID1 from MM_DB DM则结果1,12,22,11,2只有union第二部分select DM.OLD_ID,DM.ID ID1 from MM_DB DM与第一部分合并后才能产生重复值因为第一部分是主键数据不存在重复。比较特殊的情况如下DS.MID如果都匹配到(1,2),(2,1)有以下几种情况能匹配上1、1或2、2或1、2,那么这种情况union和or关联最终结果都是4行那么符合预期结果。因此可以直接用union去改写。附加验证例子create table t1(id1 int primary key,c1 int);insert into t1 values(1,1),(2,3),(3,3);commit;create table t2(id int primary key,c2 int);insert into t2 values(1,2),(2,1),(3,3),(4,4);commit;2.2 、场景2 exists/not exists子句中一张表不同列or关联同一列selectcount(1)from MM_S DS whereDS.BCODE0402AND DS.acqty0AND DS.uqty0AND DS.USin(1,2)AND exists(select1from MM_DB DM where(DS.MIDDM.ID orDS.MIDDM.OLD_ID));类似于left join or关联的做法与之不同的是union可以用union all减少去重因为exsist和not exsits只是判断是否存在它具有隐性去重的特性所以直接用union all即可。3、等价改写3.1、场景1 left join or关联同一张表不同列场景1 等价改写如下select NVL(DM.IDS,DS.MID) newMID,DS.*from MM_S DSleft join (select ID as ID,ID as IDS from MM_DB DMunion select old_id,id from mm_db dm) DMon (DS.MIDDM.ID )where DS.BCODE‘0402’AND DS.acqty0AND DS.uqty0AND DS.US in (1,2);3.2、场景2 exists/not exists子句中一张表不同列or关联同一列场景2等价改写如下selectcount(1)from MM_S DS whereDS.BCODE0402AND DS.acqty0AND DS.uqty0AND DS.USin(1,2)AND exists(select1from(select ID as ID,ID as IDS from MM_DB DM union allselectold_id,id from mm_db)dm where(DS.MIDDM.ID));4、小结1 以上的场景有个特殊的前置条件就是用来or判断关联的列中有主键。2如果只是判断存在性包括exists/not exists/in/not in这样的场景子句具有隐性去重特点可以将or用union all思路去替代改写。此次带来两个or关联场景改写后续如果有更多复杂场景再总结分享。