请求优化双表关联UPDATE查询：改写慢查询以提升性能

原语句核心问题 #

你写的语句里重复引用了dim_dummy表（sub和sub1）但未添加关联条件，会产生笛卡尔积——每一条符合START_DATE < '2018-01-01'的记录，都会和rate_dim里匹配的记录再与sub1的所有记录关联，数据量直接激增，这是耗时过长的主要原因。

简化后的查询语句 #

直接关联目标表和汇率表，去掉多余的表引用：

UPDATE dm_digital_media.dim_dummy sub
SET sub.SUBS_ONE_TIME_TOTAL_USD = sub.SUBS_ONE_TIME_TOTAL_LOCAL_CURR * NVL(exch.exchange_rate, 1)
FROM dm_digital_media.rate_dim exch
WHERE sub.currency = exch.exchange_source
  AND sub.START_DATE < '2018-01-01'
  AND exch.exchange_target = 'USD'
  AND exch.begin_effect_date = '2018-01-01'
  AND exch.exchange_period = 'Monthly';

额外性能优化建议 #

• 添加索引：
  • 给dim_dummy建复合索引，加速筛选和关联：

    CREATE INDEX idx_dim_dummy_start_curr ON dm_digital_media.dim_dummy(START_DATE, currency);
    

  • 给rate_dim建复合索引，快速定位汇率数据：

    CREATE INDEX idx_rate_dim_match ON dm_digital_media.rate_dim(exchange_source, exchange_target, begin_effect_date, exchange_period);
    

• 分批更新：如果dim_dummy表数据量极大，可按主键或日期分批执行更新，避免锁表太久：

  UPDATE dm_digital_media.dim_dummy sub
  SET sub.SUBS_ONE_TIME_TOTAL_USD = sub.SUBS_ONE_TIME_TOTAL_LOCAL_CURR * NVL(exch.exchange_rate, 1)
  FROM dm_digital_media.rate_dim exch
  WHERE sub.currency = exch.exchange_source
    AND sub.START_DATE < '2018-01-01'
    AND exch.exchange_target = 'USD'
    AND exch.begin_effect_date = '2018-01-01'
    AND exch.exchange_period = 'Monthly'
    AND sub.id BETWEEN 1 AND 10000; -- 按主键分批，调整数值适配你的表结构
  

内容的提问来源于stack exchange，提问作者Michael