Oracle表百万级记录批量对比更新方法及SQL实现咨询

完全可以通过全批量SQL完成两表对比和更新操作，无需任何循环迭代逻辑。只要提前为关联字段建好索引，2000万级数据量的全流程耗时可以控制在10分钟以内，完全满足30分钟的非功能要求。

• 首日初始化：创建Master表，字段与CSV结构完全一致，额外新增offset INT类型字段允许为空，将唯一ID列设为主键。
• 每日流程前置：先清空Daily表，将当日CSV全量导入Daily表，导入完成后给Daily表的唯一ID列创建临时索引，可大幅降低后续关联操作耗时。

所有操作均为全批量执行，数据库底层会自动优化执行路径，无逐行处理开销。

支持MERGE语法的数据库（Oracle、PostgreSQL 15+、SQL Server） #

-- 同时处理规则a（新增Daily独有的记录）和规则b（匹配到的记录offset重置为0）
MERGE INTO Master m
USING Daily d
ON m.唯一Id = d.唯一Id
WHEN MATCHED THEN
  UPDATE SET m.offset = 0
WHEN NOT MATCHED BY TARGET THEN
  INSERT (CSV字段1, CSV字段2, ..., offset)
  VALUES (d.CSV字段1, d.CSV字段2, ..., NULL);

-- 批量处理规则c：仅Master存在的记录offset加1
UPDATE Master m
LEFT JOIN Daily d ON m.唯一Id = d.唯一Id
SET m.offset = m.offset + 1
WHERE d.唯一Id IS NULL AND m.offset IS NOT NULL;

MySQL兼容方案 #

-- 处理规则b：匹配到的记录offset重置为0
UPDATE Master m
INNER JOIN Daily d ON m.唯一Id = d.唯一Id
SET m.offset = 0;

-- 处理规则a：插入Daily独有的记录
INSERT INTO Master (CSV字段1, CSV字段2, ..., offset)
SELECT d.CSV字段1, d.CSV字段2, ..., NULL
FROM Daily d
LEFT JOIN Master m ON d.唯一Id = m.唯一Id
WHERE m.唯一Id IS NULL;

-- 处理规则c：仅Master存在的记录offset加1
UPDATE Master m
LEFT JOIN Daily d ON m.唯一Id = d.唯一Id
SET m.offset = m.offset + 1
WHERE d.唯一Id IS NULL AND m.offset IS NOT NULL;

所有导出和清理同样用批量语句完成：

• 导出新增文件：执行SELECT * FROM Master WHERE offset IS NULL;，导出查询结果即可
• 导出移除文件：执行SELECT * FROM Master WHERE offset > 100;，导出查询结果即可
• 导出累计文件：执行SELECT * FROM Master WHERE offset <= 100;，导出查询结果即可
• 批量清理过期记录：执行DELETE FROM Master WHERE offset > 100;

• 确保Master表的唯一ID列为主键，Daily表的唯一ID列有索引，避免全表扫描
• 导入CSV时可临时关闭数据库的同步刷盘、事务日志强一致性等配置，导入完成后恢复原有配置，可大幅提升导入速度
• 如果数据库支持并行查询，开启并行执行可以进一步缩短全流程耗时

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