针对你这个5000万+行的Oracle表需求，直接给每行做子查询关联更新绝对会性能爆炸，得用先聚合映射再批量关联的思路，下面是具体实现方案和性能优化建议：

核心SQL方案（一次性更新） #

这种方案适合系统资源充足的情况，先把每个unique_id对应的s_date和e_date值提前聚合出来，再通过MERGE语句批量更新原表：

WITH date_mapping AS (
    SELECT 
        unique_id,
        -- 提取当前unique_id对应的s_date值
        MAX(CASE WHEN col_id = 6 AND col_nm = 'my date' AND sequ = 1 THEN col_val END) AS s_date_val,
        -- 提取当前unique_id对应的e_date值
        MAX(CASE WHEN col_id = 7 AND col_nm = 'my date 1' AND sequ = 2 THEN col_val END) AS e_date_val
    FROM table1
    GROUP BY unique_id
)
MERGE INTO table1 t
USING date_mapping dm
ON (t.unique_id = dm.unique_id)
WHEN MATCHED THEN UPDATE 
SET t.s_date = dm.s_date_val,
    t.e_date = dm.e_date_val;

方案说明 #

• date_mapping CTE只对原表做一次全表扫描，聚合出每个unique_id对应的目标日期值，这个结果集的行数等于unique_id的去重数量，远小于5000万，关联更新时效率极高。
• 用MAX(CASE...)是因为每个unique_id对应符合条件的行只会有1条，MAX/MIN都能准确提取到对应的col_val，避免返回多行报错。

分批更新方案（适合资源有限的场景） #

如果一次性更新5000万行占用的undo/redo资源太多，导致系统卡顿，可以用PL/SQL分批处理，每次更新一部分unique_id的数据：

DECLARE
    v_min_id NUMBER;
    v_max_id NUMBER;
    v_batch_size NUMBER := 100000; -- 可根据系统性能调整，比如每次处理10万条unique_id对应的数据
BEGIN
    -- 获取unique_id的范围
    SELECT MIN(unique_id), MAX(unique_id) INTO v_min_id, v_max_id FROM table1;
    
    -- 分批处理
    FOR i IN v_min_id..v_max_id BY v_batch_size LOOP
        MERGE INTO table1 t
        USING (
            SELECT 
                unique_id,
                MAX(CASE WHEN col_id = 6 AND col_nm = 'my date' AND sequ = 1 THEN col_val END) AS s_date_val,
                MAX(CASE WHEN col_id = 7 AND col_nm = 'my date 1' AND sequ = 2 THEN col_val END) AS e_date_val
            FROM table1
            WHERE unique_id BETWEEN i AND i + v_batch_size - 1
            GROUP BY unique_id
        ) dm
        ON (t.unique_id = dm.unique_id)
        WHEN MATCHED THEN UPDATE 
        SET t.s_date = dm.s_date_val,
            t.e_date = dm.e_date_val;
        
        COMMIT; -- 每批提交一次，释放undo资源
    END LOOP;
END;
/

性能优化关键点 #

1. 确保unique_id上有索引：
   创建索引可以大幅加速GROUP BY和关联更新的过程：

   CREATE INDEX idx_table1_unique_id ON table1(unique_id);
   

   如果已经存在则无需重复创建。

2. 添加组合索引加速聚合查询：
   为筛选条件(col_id、col_nm、sequ)和unique_id创建组合索引，并且包含col_val，这样聚合时不需要回表查询数据：

   CREATE INDEX idx_table1_date_keys ON table1(unique_id, col_id, col_nm, sequ) INCLUDE (col_val);
   

3. 避免全表扫描重复执行：
   我们的方案只对原表做1次（或分批多次）全表扫描，远优于每行执行子查询的N次扫描，这是大表性能优化的核心。

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