Oracle亿级Versioned Table重复地址记录删除与更新技术问询

针对数十亿条记录的版本化地址表（Versioned Table），直接逐行删除重复数据会产生巨量日志、占用大量资源，效率极低。下面是一套高效的批量处理方案，核心思路是用新表替换原表而非直接删除：

步骤1：识别重复地址分组 #

先定位所有存在重复记录的地址，确认需要处理的分组：

SELECT address, COUNT(*) AS duplicate_count
FROM Address
GROUP BY address
HAVING COUNT(*) > 1;

示例返回结果：

步骤2：插入去重后的统一记录 #

为每个重复地址生成唯一的新adr_id，并插入ver_id=0的统一记录：

-- 用MAX(adr_id)+自增序号确保新ID不冲突，也可直接用数据库自带序列
INSERT INTO Address (adr_id, ver_id, address)
SELECT
    (SELECT COALESCE(MAX(adr_id), 0) FROM Address) + ROW_NUMBER() OVER (ORDER BY address) AS adr_id,
    0 AS ver_id,
    address
FROM (
    SELECT DISTINCT address
    FROM Address
    GROUP BY address
    HAVING COUNT(*) > 1
) AS dup_addresses;

执行后表中会新增目标记录：

步骤3：批量替换原表（核心优化） #

绝对不要直接删除数十亿条记录，改用「新表存储去重后数据+原子替换」的方式：

1. 创建新表，包含所有非重复记录+刚插入的统一记录：

CREATE TABLE Address_new AS
-- 保留原本无重复的单条记录
SELECT * FROM Address
WHERE address NOT IN (
    SELECT address
    FROM Address
    GROUP BY address
    HAVING COUNT(*) > 1
)
UNION ALL
-- 保留刚插入的去重统一记录
SELECT * FROM Address
WHERE ver_id = 0 AND address IN (
    SELECT address
    FROM Address
    GROUP BY address
    HAVING COUNT(*) > 1
);

2. 给新表创建与原表完全一致的索引、主键、约束，保证查询性能和原表一致。
3. 原子替换原表（瞬时完成，无数据不一致窗口）：
   • MySQL/PostgreSQL：

     RENAME TABLE Address TO Address_old, Address_new TO Address;
     

   • Oracle：

     ALTER TABLE Address RENAME TO Address_old;
     ALTER TABLE Address_new RENAME TO Address;
     

4. 业务低峰期删除旧表Address_old即可。

关键注意事项 #

• 操作前必须全量备份原表，防止数据丢失。
• 选择业务低峰期执行，避免对线上服务造成影响。
• 若数据库支持分区（如Oracle分区表、MySQL分区），可按address字段分区，直接交换分区删除重复数据，效率会更高。
• 生成新adr_id时，优先使用数据库自带的序列（如Oracle SEQUENCE、MySQL AUTO_INCREMENT），避免手动计算出现冲突。

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