oralce sql优化-包含多表not exists转left join，一个表查询重复数据

2021/6/16 19:51:08

编程Tag： name 多表 exists oralce upper 02 idno contrilsid pnb

本文主要是介绍oralce sql优化-包含多表not exists转left join，一个表查询重复数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

出现场景：

因上一篇 Oacle锁表,ORA-00054 出现锁表的原因是由于存储过程校验执行慢导致的，因此本篇对涉及到的存储过程中涉及到，多表关联查询是否存在导入信息，一个表中根据多个字段查询是否有重复信息，进行优化

优化分两部分

1、根据临时表关联业务表，校验未查询到相应的人员信息

原型：

update contributiontemp a
     set a.chectoutstatus = '02',
         a.chectoutinfo   = a.chectoutinfo || '第' || (a.expandfield1 + 1) ||
                            '行,未查询到相应的人员信息.' || chr(13) || chr(10)
   where a.ExpandField2 = '01' and  not exists (select distinct pnb.idno from PN_B_Staff pnb
         left join PN_B_StaffComPlan pns on pns.staffid=pnb.staffid
         where pns.PlanID =a.planid
         and upper(pnb.idno)=upper(a.idno)
         and pnb.name = a.name
         and pnb.idtype=decode(a.idtype,'身份证','01','居民身份证','01','军人证','02','其他','09','居民户口簿','10','驾驶证','11','军官证','02','士兵证','12','军官离退休证','13','中国护照','14','异常身份证','15','港澳通行证','20','台湾通行证','21','回乡证','16','外国护照','17','旅行证','18','居留证件','19','其它','09','')
         )
         and a.expandfield10 ='20210616152859459551' and a.chectoutstatus is null;

改造后：

update contributiontemp ctm
     set ctm.chectoutstatus = '02',
         ctm.chectoutinfo   = ctm.chectoutinfo || '第' || (ctm.expandfield1 + 1) ||
                            '行,未查询到相应的人员信息.' || chr(13) || chr(10)
 WHERE   exists(           
            select distinct a.contrilsid
            from contributiontemp a
                 left join 
                 (--1、取本计划所有的人员三证信息
                      SELECT distinct pnb.name, pnb.idno,pnb.idtype
                      FROM PN_B_StaffComPlan pns
                           left join PN_B_Staff pnb on pns.staffid=pnb.staffid
                      WHERE planid = '20210315111531000001'
                  ) pnstd
                  on upper(pnstd.idno)=upper(a.idno) and pnstd.name = a.name
                  and pnstd.idtype=decode(a.idtype,'身份证','01','居民身份证','01','军人证','02','其他','09','居民户口簿','10','驾驶证','11','军官证','02','士兵证','12','军官离退休证','13','中国护照','14','异常身份证','15','港澳通行证','20','台湾通行证','21','回乡证','16','外国护照','17','旅行证','18','居留证件','19','其它','09','')
             WHERE pnstd.idno is NULL
                   and a.ExpandField2 = '01'
                   and a.chectoutstatus is null
                   and a.expandfield10 ='20210616152859459551'
                   and ctm.contrilsid = a.contrilsid
             )

结论：经测试，在20w条数据上，改造后的sql，执行效率是原型sql的30倍以上，大大提高了效率；

2、在临时表中查询重复信息

原型：

update CONTRIBUTIONTEMP c
     set c.repeatcount    = '02',
         c.chectoutstatus = '02',
         c.chectoutinfo   = c.chectoutinfo || '第' ||(expandfield1+1) ||
                            '行,导入信息重复.' || chr(13) || chr(10)
   where c.ExpandField2 = '01'
     and c.expandfield10 = '20210616152859459551' and c.chectoutstatus is null
     and c.expandfield1 in
         (select distinct b.expandfield1
            from CONTRIBUTIONTEMP b
            left join CONTRIBUTIONTEMP d
              on b.expandfield10 = d.expandfield10
           where b.expandfield10 = '20210616152859459551'
                 and b.contrilsid <> d.contrilsid
                 and b.name = d.name
                 and upper(b.idno) = upper(d.idno)
                  );