基于复合键的Oracle 20亿条记录表增量检测方案问询

首日初始加载数据 #

次日加载数据 #

需识别的增量记录（次日） #

问题背景 #

• 初始需处理约20亿条记录表数据；
• 需快速识别增量以完成后续处理。

咨询问题 #

1. 尤其在生产停机期间，识别增量是否属于耗时操作？
2. 对于包含3个数值列（其中id与key构成复合键）的表，识别增量所需时长为多少？

已尝试方案 #

使用全连接（full join）结合nvl条件提取增量，但该方案成本较高。

SELECT
    nvl(node1.id, node2.id) id,
    nvl(node1.key, node2.key) key,
    nvl(node1.fkid, node2.fkid) fkid
FROM
    TABLE_DAY_1       node1
    FULL JOIN TABLE_DAY_2   node2 ON node2.id = node1.id
WHERE
    node2.id IS NULL
    OR node1.id IS NULL;

解答 #

问题1：生产停机期间识别增量是否耗时？

这完全取决于你采用的方法和数据规模。像你当前使用的全连接方式，在20亿数据量级下必然耗时——全连接需要对两张大表做全局匹配，IO和计算量极大，会占用大量系统资源，拖慢整个流程。但如果换成更高效的方案（比如基于复合键的差集查询、利用索引或分区），可以大幅缩短耗时，完全能在常规停机窗口内完成。

问题2：3数值列（id+key为复合键）的表识别增量时长？

没有固定时长，核心影响因素包括：

• 存储介质：SSD的读写速度远快于HDD，能大幅缩短数据加载时间；
• 计算引擎/数据库性能：分布式引擎（如Spark、Flink）比单机数据库处理大数据的效率高几个量级；
• 索引情况：给(id, key)复合键建立索引后，能快速定位匹配记录，避免全表扫描；
• 增量比例：如果增量仅占总数据的1%，处理速度会比增量占50%快得多；
• 计算资源：CPU核数、内存大小直接影响并行处理能力。

举个实际场景：用Spark集群处理20亿条数据，基于复合键做差集查询，在资源充足（几十台节点）的情况下，可能几十分钟到1小时就能完成；但如果用单机数据库做全连接，可能需要数小时甚至更久。

优化建议

1. 替换全连接为高效差集查询：
   如果只需要次日新增的记录，直接查询次日表中不在首日表的记录即可：

   SELECT id, key, fkid
   FROM TABLE_DAY_2
   WHERE (id, key) NOT IN (SELECT id, key FROM TABLE_DAY_1);
   

   或者用LEFT JOIN定位新增：

   SELECT t2.id, t2.key, t2.fkid
   FROM TABLE_DAY_2 t2
   LEFT JOIN TABLE_DAY_1 t1 ON t2.id = t1.id AND t2.key = t1.key
   WHERE t1.id IS NULL;
   

   这两种方法的计算量远小于全连接，尤其是在复合键有索引的情况下，效率会提升数倍。

2. 建立复合键索引：给(id, key)创建唯一索引，让数据库/引擎能快速通过键值匹配记录，避免全表扫描。

3. 利用分区或分桶：如果数据按日期分区存储，只需扫描对应日期的分区即可，不用处理全量数据；分布式引擎中对复合键分桶，能减少数据 shuffle 量，提升并行处理效率。

4. 使用CDC增量日志：如果业务允许，借助数据库binlog或CDC（变更数据捕获）工具，直接从日志中提取增量数据，无需对比全量表，这是最快的增量识别方式。

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