Oracle批量更新1.5亿行表序列字段的高效方法咨询

针对超大规模数据的序列更新操作，直接用UPDATE语句即便加了并行，也会因海量undo/redo日志生成、锁资源竞争等问题拖慢速度，以下是几个更高效的处理方案：

方案一：用CTAS（Create Table As Select）替代更新（推荐） #

这是大数据量场景下最快的处理方式，直接创建包含序列值的新表，绕过更新操作的undo/redo开销：

1. 执行CTAS语句创建新表，指定并行和NOLOGGING减少日志生成：

   CREATE TABLE rpm_future_retail_new NOLOGGING PARALLEL 50 
   AS SELECT rpm_future_retail_seq.NEXTVAL AS future_retail_id, t.* 
      FROM rpm_future_retail_tmp t;
   

   注意：NOLOGGING会跳过redo日志生成，大幅提速，但操作完成后务必对新表做一次备份，避免数据恢复风险。
2. 重命名原表并替换：

   ALTER TABLE rpm_future_retail_tmp RENAME TO rpm_future_retail_old;
   ALTER TABLE rpm_future_retail_new RENAME TO rpm_future_retail_tmp;
   

3. 重建原表的索引、约束、触发器等对象，可根据实际需求调整并行度。

方案二：分批更新 #

如果不能替换原表，可按数据范围分批更新，避免一次性占用过多资源：

1. 选择可拆分的字段（比如主键ID、rowid哈希值），循环分批更新并每次提交：

   DECLARE
     v_batch_size NUMBER := 1000000; -- 每次更新100万条，可根据服务器性能调整
     v_total_rows NUMBER;
     v_processed_rows NUMBER := 0;
   BEGIN
     SELECT COUNT(*) INTO v_total_rows FROM rpm_future_retail_tmp;
     WHILE v_processed_rows < v_total_rows LOOP
       UPDATE /*+ parallel(c,50) */ rpm_future_retail_tmp c
       SET future_retail_id = rpm_future_retail_seq.NEXTVAL
       WHERE future_retail_id IS NULL -- 假设该列初始为空，避免重复更新
       AND ROWNUM <= v_batch_size;
       COMMIT;
       v_processed_rows := v_processed_rows + SQL%ROWCOUNT;
     END LOOP;
   END;
   /
   

   若表有主键，用主键范围拆分更高效，可避免每次全表扫描。

方案三：优化序列配置 #

增大序列的缓存值，减少每次取NEXTVAL时的锁竞争和IO开销：

ALTER SEQUENCE rpm_future_retail_seq CACHE 10000; -- 默认缓存通常是20，可根据实际调至更高

方案四：利用分区表特性 #

如果你的表是分区表，可以按分区单独执行更新，每个分区并行处理，单分区数据量小，速度会更快：

UPDATE /*+ parallel(c,50) */ rpm_future_retail_tmp PARTITION(partition_name) c
SET future_retail_id = rpm_future_retail_seq.NEXTVAL;

循环处理所有分区即可。

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