Oracle中基于枚举值批量迁移表B数据至表A的高效方案咨询

原方案里的UPDATE语句会对表B执行两次独立的关联查询，数据量较大时会重复扫描表B，再加上如果B(parent_id, type)没有合适索引，性能会非常差。下面是几种更高效的实现方式：

1. 先聚合表B数据再关联更新 #

先把表B中同一个parent_id的type=1和type=2的info合并成一行，再和表A关联更新，这样只需要扫描表B一次：

方式一：使用条件聚合 #

-- 先创建临时聚合结果（也可以直接在MERGE里用子查询）
CREATE TABLE B_AGG AS
SELECT 
    parent_id,
    MAX(CASE WHEN type = 1 THEN info END) AS info1,
    MAX(CASE WHEN type = 2 THEN info END) AS info2
FROM B
GROUP BY parent_id;

-- 用MERGE更新表A
MERGE INTO A
USING B_AGG
ON (A.id = B_AGG.parent_id)
WHEN MATCHED THEN
    UPDATE SET 
        A.info1 = B_AGG.info1,
        A.info2 = B_AGG.info2;

-- 清理临时表
DROP TABLE B_AGG;

方式二：使用PIVOT语法（Oracle 11g+支持） #

MERGE INTO A
USING (
    SELECT parent_id, info1, info2
    FROM B
    PIVOT (
        MAX(info) FOR type IN (1 AS info1, 2 AS info2)
    )
) B_PIV
ON (A.id = B_PIV.parent_id)
WHEN MATCHED THEN
    UPDATE SET 
        A.info1 = B_PIV.info1,
        A.info2 = B_PIV.info2;

2. 索引优化 #

在执行更新前，给表B创建联合索引，能大幅提升关联查询的速度：

CREATE INDEX idx_b_parent_type ON B(parent_id, type);

如果后续要删除表B，这个索引可以不用保留，更新完成后直接和表B一起删除即可。

3. 并行执行加速 #

如果数据库开启了并行功能，可以给操作加上并行提示，利用多CPU资源加快处理：

-- 并行创建聚合表
CREATE TABLE B_AGG PARALLEL 4 AS
SELECT 
    parent_id,
    MAX(CASE WHEN type = 1 THEN info END) AS info1,
    MAX(CASE WHEN type = 2 THEN info END) AS info2
FROM B
GROUP BY parent_id;

-- 并行MERGE
MERGE /*+ PARALLEL(A, 4) PARALLEL(B_PIV, 4) */ INTO A
USING (
    SELECT parent_id, info1, info2
    FROM B
    PIVOT (
        MAX(info) FOR type IN (1 AS info1, 2 AS info2)
    )
) B_PIV
ON (A.id = B_PIV.parent_id)
WHEN MATCHED THEN
    UPDATE SET 
        A.info1 = B_PIV.info1,
        A.info2 = B_PIV.info2;

注意：并行度根据服务器CPU核心数调整，不要设置过高导致资源耗尽。

4. 超大批量数据的分批更新 #

如果表A数据量达到千万级以上，一次性更新可能导致UNDO日志暴涨、锁表时间过长，可以分批处理：

DECLARE
    v_batch_size NUMBER := 10000; -- 每批处理1万条
    v_max_id NUMBER;
    v_current_id NUMBER := 0;
BEGIN
    SELECT MAX(id) INTO v_max_id FROM A;
    
    WHILE v_current_id < v_max_id LOOP
        UPDATE A
        SET 
            info1 = (SELECT info FROM B WHERE type = 1 AND A.id = B.parent_id),
            info2 = (SELECT info FROM B WHERE type = 2 AND A.id = B.parent_id)
        WHERE id > v_current_id 
          AND id <= v_current_id + v_batch_size;
        
        COMMIT; -- 每批提交一次，释放UNDO空间
        v_current_id := v_current_id + v_batch_size;
    END LOOP;
END;
/

这种方式可以减少单次操作的资源占用，避免长时间锁表，但总耗时可能比并行聚合更新长，适合极端大数据量场景。

验证数据一致性 #

更新完成后，建议验证数据是否正确，比如检查是否存在info1或info2为空的情况（根据业务需求，如果每个parent_id确实有两条数据，空值可能表示数据异常）：

SELECT COUNT(*) 
FROM A
WHERE info1 IS NULL OR info2 IS NULL;

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