Oracle SQL中内查询较快时如何优化MERGE语句性能？

针对你提供的MERGE语句（目标表FUR_FACT_T900万条记录，源查询返回5万条），以下是具体优化措施：

1. 消除重复子查询，减少冗余计算 #

原语句中两次重复执行相同的DISTINCT子查询，可将其提取为公共CTE缓存结果，同时用JOIN替代IN（Oracle对JOIN的优化逻辑更高效）：

MERGE --+ parallel(targ 4) parallel(src 4)
INTO FUR_FACT_T targ
USING (
    WITH TARGET_KEYS AS (
        SELECT DISTINCT 
            ITEM_NO,
            ITEM_TYPE,
            SUP_CODE AS CODE_SUP,
            SUP_TYPE AS TYPE_SUP,
            SUP_CODE_RAGREE AS CODE_RU,
            SUP_TYPE_RAGREE AS TYPE_RU
        FROM TEMP.FACT_ITEM_TEMP_T 
        WHERE VER_DELETE_DATE IS NOT NULL
    ),
    LATEST_DATA AS (
        SELECT 
            ITEM_NO,
            ITEM_TYPE,
            CODE_SUP,
            TYPE_SUP,
            CODE_RU,
            TYPE_RU,
            PERCENT_SHARE,
            VALID_FROM,
            VALID_TO,
            MAX(VALID_FROM) OVER (PARTITION BY ITEM_NO,ITEM_TYPE,CODE_SUP,TYPE_SUP,CODE_RU,TYPE_RU) AS MAX_VALID_FROM
        FROM FUR_FACT_T f
        JOIN TARGET_KEYS k 
            ON f.ITEM_NO = k.ITEM_NO
            AND f.ITEM_TYPE = k.ITEM_TYPE
            AND f.CODE_SUP = k.CODE_SUP
            AND f.TYPE_SUP = k.TYPE_SUP
            AND f.CODE_RU = k.CODE_RU
            AND f.TYPE_RU = k.TYPE_RU
        WHERE date '2023-01-11' >= f.VALID_FROM
          AND date '2023-01-11' <= nvl(f.VALID_TO, DATE '9999-12-31')
    )
    SELECT 
        p.ITEM_NO,
        p.ITEM_TYPE,
        p.CODE_SUP,
        p.TYPE_SUP,
        p.CODE_RU,
        p.TYPE_RU,
        p.PERCENT_SHARE,
        q.VALID_FROM,
        q.VALID_TO
    FROM TEMP.FACT_ITEM_DATA_TEMP_T p
    JOIN TARGET_KEYS k 
        ON p.ITEM_NO = k.ITEM_NO
        AND p.ITEM_TYPE = k.ITEM_TYPE
        AND p.CODE_SUP = k.CODE_SUP
        AND p.TYPE_SUP = k.TYPE_SUP
        AND p.CODE_RU = k.CODE_RU
        AND p.TYPE_RU = k.TYPE_RU
    LEFT JOIN LATEST_DATA q
        ON p.ITEM_NO = q.ITEM_NO
        AND p.ITEM_TYPE = q.ITEM_TYPE
        AND p.CODE_SUP = q.CODE_SUP
        AND p.TYPE_SUP = q.TYPE_SUP
        AND p.CODE_RU = q.CODE_RU
        AND p.TYPE_RU = q.TYPE_RU
        AND q.VALID_FROM = q.MAX_VALID_FROM
    WHERE p.VER_DELETE_DATE IS NOT NULL
) src
ON (
    targ.ITEM_NO = src.ITEM_NO
    AND targ.ITEM_TYPE = src.ITEM_TYPE
    AND targ.CODE_SUP = src.CODE_SUP
    AND targ.TYPE_SUP = src.TYPE_SUP
    AND targ.CODE_RU = src.CODE_RU
    AND targ.TYPE_RU = src.TYPE_RU
    AND targ.VALID_FROM = src.VALID_FROM
)
WHEN MATCHED THEN
    UPDATE SET
        targ.PERCENT_SHARE = src.PERCENT_SHARE,
        targ.VALID_TO = src.VALID_TO;

注：原语句中src未选择VALID_FROM和VALID_TO，但UPDATE和关联条件需要用到，已修正该问题。

2. 正确配置并行执行 #

• 仅给目标表加并行提示不够，需同时给源查询（src）指定并行度，比如--+ parallel(targ 4) parallel(src 4)，并行度建议设为CPU核心数的1-2倍。
• 执行前开启会话并行DML：ALTER SESSION ENABLE PARALLEL DML;，否则并行提示可能不生效。
• 可临时调整目标表并行度：ALTER TABLE FUR_FACT_T PARALLEL 4;，不需要时可以改回：ALTER TABLE FUR_FACT_T NOPARALLEL;。

3. 添加针对性索引 #

索引是提升MERGE效率的核心，需针对关联和过滤条件创建：

• 目标表关联索引：CREATE INDEX IDX_FUR_FACT_MERGE ON FUR_FACT_T(ITEM_NO, ITEM_TYPE, CODE_SUP, TYPE_SUP, CODE_RU, TYPE_RU, VALID_FROM);，这是MERGE关联的所有字段，能快速定位待更新行。
• 临时表过滤+关联索引：

  CREATE INDEX IDX_TEMP_ITEM_KEYS ON TEMP.FACT_ITEM_TEMP_T(VER_DELETE_DATE, ITEM_NO, ITEM_TYPE, SUP_CODE, SUP_TYPE, SUP_CODE_RAGREE, SUP_TYPE_RAGREE);
  CREATE INDEX IDX_TEMP_DATA_KEYS ON TEMP.FACT_ITEM_DATA_TEMP_T(VER_DELETE_DATE, ITEM_NO, ITEM_TYPE, CODE_SUP, TYPE_SUP, CODE_RU, TYPE_RU);
  

• 目标表过滤条件索引：CREATE INDEX IDX_FUR_FACT_VALID ON FUR_FACT_T(ITEM_NO, ITEM_TYPE, CODE_SUP, TYPE_SUP, CODE_RU, TYPE_RU, VALID_FROM, VALID_TO);，加速LATEST_DATA中的时间范围过滤。

4. 简化窗口函数逻辑 #

原LATEST_DATA用窗口函数取最大VALID_FROM，可改为分组聚合后关联，减少全表扫描开销：

LATEST_DATA AS (
    SELECT 
        f.ITEM_NO,
        f.ITEM_TYPE,
        f.CODE_SUP,
        f.TYPE_SUP,
        f.CODE_RU,
        f.TYPE_RU,
        f.PERCENT_SHARE,
        f.VALID_FROM,
        f.VALID_TO
    FROM FUR_FACT_T f
    JOIN (
        SELECT 
            ITEM_NO,
            ITEM_TYPE,
            CODE_SUP,
            TYPE_SUP,
            CODE_RU,
            TYPE_RU,
            MAX(VALID_FROM) AS MAX_VALID_FROM
        FROM FUR_FACT_T
        JOIN TARGET_KEYS k 
            ON ITEM_NO = k.ITEM_NO
            AND ITEM_TYPE = k.ITEM_TYPE
            AND CODE_SUP = k.CODE_SUP
            AND TYPE_SUP = k.TYPE_SUP
            AND CODE_RU = k.CODE_RU
            AND TYPE_RU = k.TYPE_RU
        WHERE date '2023-01-11' >= VALID_FROM
          AND date '2023-01-11' <= nvl(VALID_TO, DATE '9999-12-31')
        GROUP BY ITEM_NO,ITEM_TYPE,CODE_SUP,TYPE_SUP,CODE_RU,TYPE_RU
    ) g ON f.ITEM_NO = g.ITEM_NO
        AND f.ITEM_TYPE = g.ITEM_TYPE
        AND f.CODE_SUP = g.CODE_SUP
        AND f.TYPE_SUP = g.TYPE_SUP
        AND f.CODE_RU = g.CODE_RU
        AND f.TYPE_RU = g.TYPE_RU
        AND f.VALID_FROM = g.MAX_VALID_FROM
)

5. 优化JOIN类型 #

原语句用LEFT JOIN LATEST_DATA，但只有匹配到的行才会在MERGE中触发更新，未匹配的行无法命中目标表，可改为INNER JOIN减少源数据量：

SELECT 
    p.ITEM_NO,
    p.ITEM_TYPE,
    p.CODE_SUP,
    p.TYPE_SUP,
    p.CODE_RU,
    p.TYPE_RU,
    p.PERCENT_SHARE,
    q.VALID_FROM,
    q.VALID_TO
FROM TEMP.FACT_ITEM_DATA_TEMP_T p
JOIN TARGET_KEYS k 
    ON p.ITEM_NO = k.ITEM_NO
    AND p.ITEM_TYPE = k.ITEM_TYPE
    AND p.CODE_SUP = k.CODE_SUP
    AND p.TYPE_SUP = k.TYPE_SUP
    AND p.CODE_RU = k.CODE_RU
    AND p.TYPE_RU = k.TYPE_RU
JOIN LATEST_DATA q -- 替换为INNER JOIN
    ON p.ITEM_NO = q.ITEM_NO
    AND p.ITEM_TYPE = q.ITEM_TYPE
    AND p.CODE_SUP = q.CODE_SUP
    AND p.TYPE_SUP = q.TYPE_SUP
    AND p.CODE_RU = q.CODE_RU
    AND p.TYPE_RU = q.TYPE_RU
    AND q.VALID_FROM = q.MAX_VALID_FROM
WHERE p.VER_DELETE_DATE IS NOT NULL

6. 临时表预处理 #

如果临时表是一次性导入的，可提前清理无效数据：

• 删除VER_DELETE_DATE IS NULL的行，减少后续查询的数据量。
• 对临时表的关联字段做去重，避免重复处理相同键值。

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