Update Query优化求助：现有SQL运行耗时过长，求修改方案

首先指出原语句的两个核心问题：

• 逻辑错误：第二个WHERE条件CUST_UNIT_CD=(SELECT CUST_CD FROM BDM.CUST_DIM_SCD2 ...)存在字段不匹配问题——子查询返回的是CUST_CD，但要匹配的是CUST_UNIT_CD，这会导致逻辑失效，同时额外消耗计算资源。
• 性能瓶颈：嵌套子查询重复扫描BDM.CUST_DIM_SCD2表，数据量大时会大幅增加IO开销。

以下是优化后的写法及建议：

方案1：用EXISTS关联替代嵌套子查询 #

合并重复的子查询逻辑，减少表扫描次数：

UPDATE BDM.CUST_DIM_SCD2 t
SET EFF_TO_TMS = current_timestamp - 2,
    CUR_ROW_IND = 0
WHERE EXISTS (
    SELECT 1
    FROM BDM.CUST_DIM_SCD2 s
    JOIN BDM.TEMP_CUST_DIM_UPDATE u 
        ON s.CUST_CD = u.CUST_CD 
        AND s.CUST_UNIT_CD = u.CUST_UNIT_CD
    WHERE s.CUR_ROW_IND = 1
      AND t.CUST_CD = s.CUST_CD
      AND t.CUST_UNIT_CD = s.CUST_UNIT_CD
)

方案2：用JOIN方式直接更新（适配PostgreSQL、MySQL等支持JOIN更新的数据库） #

这种方式能让数据库优化器生成更高效的执行计划：

UPDATE BDM.CUST_DIM_SCD2 t
SET EFF_TO_TMS = current_timestamp - 2,
    CUR_ROW_IND = 0
FROM BDM.CUST_DIM_SCD2 s
JOIN BDM.TEMP_CUST_DIM_UPDATE u 
    ON s.CUST_CD = u.CUST_CD 
    AND s.CUST_UNIT_CD = u.CUST_UNIT_CD
WHERE s.CUR_ROW_IND = 1
  AND t.CUST_CD = s.CUST_CD
  AND t.CUST_UNIT_CD = s.CUST_UNIT_CD

额外性能优化建议 #

• 给BDM.CUST_DIM_SCD2创建组合索引：

  CREATE INDEX idx_cust_dim_scd2_cur_row ON BDM.CUST_DIM_SCD2(CUR_ROW_IND, CUST_CD, CUST_UNIT_CD);
  

  加速CUR_ROW_IND=1的筛选和关联逻辑。
• 给临时表BDM.TEMP_CUST_DIM_UPDATE创建(CUST_CD, CUST_UNIT_CD)组合索引，减少JOIN时的查找开销。
• 若TEMP_CUST_DIM_UPDATE是临时表，确保其统计信息为最新状态，帮助优化器生成最优执行计划。

内容的提问来源于stack exchange，提问作者UDAYA KUMAR R