基本信息

系统 : 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 ;