数十亿级员工地址去重与父表关联更新的高效并行方案问询

核心逻辑梳理 #

需要完成三个核心操作：

• 从address表提取唯一地址，生成新的adr_id
• 将employee表中关联旧地址的记录，批量更新为对应唯一地址的新adr_id
• 清理原address表的重复行，插入唯一地址记录

1. 生成唯一地址映射表 #

先创建临时映射表，关联旧地址组合（adr_id+ver_id）到新的唯一adr_id：

-- 创建序列用于生成新的adr_id（自增唯一ID）
CREATE SEQUENCE new_adr_seq START WITH 11 INCREMENT BY 1 CACHE 10000;

-- 创建全局临时表存储映射关系
CREATE GLOBAL TEMPORARY TABLE adr_mapping (
    old_adr_id NUMBER,
    old_ver_id NUMBER,
    new_adr_id NUMBER,
    address VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

-- 插入映射关系：相同address对应同一个new_adr_id
WITH unique_address AS (
    SELECT address, new_adr_seq.NEXTVAL AS new_adr_id
    FROM (SELECT DISTINCT address FROM address)
)
INSERT /*+ PARALLEL(8) */ INTO adr_mapping
SELECT a.adr_id, a.ver_id, ua.new_adr_id, ua.address
FROM address a
JOIN unique_address ua ON a.address = ua.address;

2. 并行更新员工表 #

针对数十亿级数据，启用并行DML加速更新：

-- 开启会话级并行DML支持
ALTER SESSION ENABLE PARALLEL DML;

-- 批量合并更新员工表的adr_id关联
MERGE /*+ PARALLEL(8) */ INTO employee e
USING adr_mapping am
ON (e.adr_id = am.old_adr_id AND e.ver_id = am.old_ver_id)
WHEN MATCHED THEN UPDATE SET e.adr_id = am.new_adr_id;

如果employee是分区表（比如按adr_id范围分区），可按分区拆分并行更新，进一步提升效率。

3. 清理并重建地址表 #

因存在复合外键约束，需先确保员工表已关联新地址，再处理原地址表：

-- 临时禁用外键约束，避免删除旧地址时触发检查
ALTER TABLE employee DISABLE CONSTRAINT emp_adr_fk;

-- 清空原地址表
TRUNCATE TABLE address;

-- 插入唯一地址记录
INSERT /*+ PARALLEL(8) */ INTO address (adr_id, ver_id, address)
SELECT DISTINCT new_adr_id, 0, address FROM adr_mapping;

-- 重新启用外键约束
ALTER TABLE employee ENABLE CONSTRAINT emp_adr_fk;

并行优化关键措施 #

针对超大规模数据，从以下维度优化性能：

1. 并行执行配置 #

• 在SQL语句中添加/*+ PARALLEL(n) */提示，n建议设为CPU核心数的2倍左右
• 提前执行ALTER SESSION ENABLE PARALLEL DML;开启并行DML支持
• 设置表级并行度：ALTER TABLE employee PARALLEL 8;、ALTER TABLE address PARALLEL 8;

2. 分区表改造 #

• 将address表按address哈希分区，employee表按adr_id范围分区，让并行任务可拆分到独立分区执行
• 利用分区交换（ALTER TABLE ... EXCHANGE PARTITION）批量处理数据，避免全表扫描

3. 存储过程并行化 #

若需保留存储过程，使用DBMS_PARALLEL_EXECUTE拆分任务：

DECLARE
    l_task_name VARCHAR2(100) := 'ADR_CLEANUP_TASK';
    l_sql_stmt VARCHAR2(1000);
BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);
    -- 按adr_id范围拆分任务，每个块处理100万条记录
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_RANGE(
        task_name => l_task_name,
        table_owner => 'YOUR_SCHEMA',
        table_name => 'ADDRESS',
        column_name => 'ADR_ID',
        chunk_size => 1000000
    );
    -- 定义单块处理逻辑
    l_sql_stmt := '
        DECLARE
            l_chunk_id NUMBER;
        BEGIN
            INSERT INTO adr_mapping
            SELECT a.adr_id, a.ver_id, ua.new_adr_id, ua.address
            FROM address a
            JOIN (SELECT DISTINCT address, new_adr_seq.NEXTVAL AS new_adr_id FROM address) ua ON a.address = ua.address
            WHERE a.adr_id BETWEEN :start_id AND :end_id;
            
            MERGE INTO employee e
            USING adr_mapping am
            ON (e.adr_id = am.old_adr_id AND e.ver_id = am.old_ver_id)
            WHEN MATCHED THEN UPDATE SET e.adr_id = am.new_adr_id;
        END;';
    -- 执行并行任务
    DBMS_PARALLEL_EXECUTE.RUN_TASK(
        task_name => l_task_name,
        sql_stmt => l_sql_stmt,
        language_flag => DBMS_SQL.NATIVE,
        parallel_level => 8
    );
    DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task_name);
END;
/

4. 索引与约束优化 #

• 更新前禁用非必要索引和外键，更新完成后重新启用，减少索引维护开销
• 在adr_mapping表上创建(old_adr_id, old_ver_id)联合索引，加速员工表的MERGE操作

注意事项 #

• 处理前务必全量备份数据，避免数据丢失
• 并行度不要过高，防止数据库资源耗尽
• 序列需设置足够大的缓存值，避免序列争用
• 超大规模数据建议分批次处理，避免单次操作占用过多资源

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