UPDATE异常SQL优化

基本信息

系统 : CentOS Linux release 7.4.1708 (Core)
产品 : OushuDB
模块 : DB
子模块 : -

描述详述

-- 该SQL逻辑太复杂，太冗余,可能存在浪费资源
-- 原始SQL 
UPDATE DEMO.FA_CH
   SET CEE =
       (SELECT PCE
          FROM DEMO.DD_CP
         WHERE UPPER(SC) =
               UPPER(DEMO.FA_CH.CEE)
           AND UPPER(SSYS) = UPPER('DSYS')
           AND UPPER(CODE_TYPE) =
               (SELECT UPPER(CODE_TYPE)
                  FROM DEMO.DD_CTD
                 WHERE UPPER(SSYS) = UPPER('DSYS')
                   AND UPPER(STN) = UPPER('SS_TAB_NAME')
                   AND UPPER(SV) = UPPER('SS_VALUE')))
 WHERE CEE IN
       (SELECT SC
          FROM DEMO.DD_CP
         WHERE UPPER(SSYS) = UPPER('DSYS')
           AND UPPER(CODE_TYPE) =
               (SELECT UPPER(CODE_TYPE)
                  FROM DEMO.DD_CTD
                 WHERE UPPER(SSYS) = UPPER('DSYS')
                   AND UPPER(STN) = UPPER('SS_TAB_NAME')
                   AND UPPER(SV) = UPPER('SS_VALUE')))
   AND EXISTS
 (SELECT 1
          FROM DEMO.DD_CTD
         WHERE UPPER(SSYS) = UPPER('DSYS')
           AND UPPER(STN) = UPPER('SS_TAB_NAME')
           AND UPPER(SV) = UPPER('SS_VALUE'))
   and DEMO.FA_CH.DW_DATA_DT = '2021-04-18';

原因

解决方案

-- 优化SQL   
UPDATE DEMO.FA_CH H 
    set CEE =M.PCE
    from DEMO.DD_CP M, 
    (
     SELECT 1 as flag
          FROM DEMO.DD_CTD
         WHERE UPPER(SSYS) = UPPER('DSYS')
           AND UPPER(STN) = UPPER('SS_TAB_NAME')
           AND UPPER(SV) = UPPER('SS_VALUE')
    ) tmp_is_work
where UPPER(M.SSYS) = UPPER('DSYS')
           AND UPPER(M.CODE_TYPE) =
               (SELECT UPPER(CODE_TYPE)
                  FROM DEMO.DD_CTD
                 WHERE UPPER(SSYS) = UPPER('DSYS')
                   AND UPPER(STN) = UPPER('SS_TAB_NAME')
                   AND UPPER(SV) = UPPER('SS_VALUE'))	   
   and H.DW_DATA_DT = '2021-04-18'
   and H.CEE  = M.SC
   and  tmp_is_work.flag=1
;