Oracle 12c大表新增自增列：3亿数据高效填充方案求助

现有Oracle 12c企业版数据库中的PARTICIPANTS_TABLE表，包含约3亿条记录，需要新增一列column_id，要求该列从1开始自增。尝试过三种方法均运行2-3小时后失败，求助高效填充方案。

已尝试的三种方法 #

1. 使用IDENTITY列

ALTER TABLE PARTICIPANTS_TABLE
ADD COLUMN_ID INTEGER GENERATED ALWAYS AS IDENTITY

2. 使用序列+全表更新

CREATE SEQUENCE SEQ_PARTICIPANTS
MINVALUE 1
MAXVALUE 400000000
INCREMENT BY 1
START WITH 1
CACHE 10000;

-- 先添加未填充的column_id，再执行更新
ALTER TABLE PARTICIPANTS_TABLE ADD COLUMN_ID INTEGER;

UPDATE PARTICIPANTS_TABLE
SET COLUMN_ID = SEQ_PARTICIPANTS.NEXTVAL;

3. 使用序列+触发器（重建表导入数据）

-- 先创建触发器，再重建空表并导入3亿条记录，通过触发器填充column_id
CREATE OR REPLACE TRIGGER TRIG_PARTICIPANTS
BEFORE INSERT
ON PARTICIPANTS_TABLE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
   IF(:NEW.COLUMN_ID IS NULL) THEN
      SELECT SEQ_PARTICIPANTS.NEXTVAL
      INTO :NEW.COLUMN_ID
      FROM DUAL;
   END IF;
END;
/

方案1：CTAS并行创建新表（最优选择） #

通过CREATE TABLE AS SELECT并行生成自增列，避免原表长时间锁和大量日志开销，是3亿级数据最快的处理方式：

1. （可选）禁用原表非必要的约束、索引，加快新表创建速度
2. 并行创建包含自增列的新表：

CREATE TABLE PARTICIPANTS_TABLE_NEW PARALLEL 16 -- 根据服务器CPU核心数调整并行度
NOLOGGING -- 关闭日志生成，大幅提速（操作前需确保有备份）
AS
SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS COLUMN_ID, -- ORDER BY NULL避免不必要排序，需特定顺序可替换为目标列
       t.*
FROM PARTICIPANTS_TABLE t;

3. 验证数据无误后，替换原表：

ALTER TABLE PARTICIPANTS_TABLE RENAME TO PARTICIPANTS_TABLE_OLD;
ALTER TABLE PARTICIPANTS_TABLE_NEW RENAME TO PARTICIPANTS_TABLE;

4. 重建原表的主键、索引、约束，收集统计信息：

-- 示例：重建主键
ALTER TABLE PARTICIPANTS_TABLE ADD CONSTRAINT PK_PARTICIPANTS PRIMARY KEY (XXX, COLUMN_ID);

-- 并行重建索引
CREATE INDEX IDX_PARTICIPANTS_XXX ON PARTICIPANTS_TABLE(XXX) PARALLEL 16;

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'PARTICIPANTS_TABLE', CASCADE => TRUE);

方案2：DBMS_PARALLEL_EXECUTE并行更新原表 #

若无法重建表，可利用Oracle并行执行包将表拆分为多个块，并行更新：

1. 添加空的column_id列：

ALTER TABLE PARTICIPANTS_TABLE ADD COLUMN_ID INTEGER;
ALTER TABLE PARTICIPANTS_TABLE PARALLEL 16;
ALTER TABLE PARTICIPANTS_TABLE NOLOGGING;

2. 创建并行更新任务：

DECLARE
  l_task_name VARCHAR2(100) := 'UPDATE_PARTICIPANTS_ID';
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);
  -- 按ROWID拆分表，每个块包含100万条记录
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
    task_name => l_task_name,
    table_owner => 'SCHEMA_NAME',
    table_name => 'PARTICIPANTS_TABLE',
    by_row => TRUE,
    chunk_size => 1000000
  );
  -- 并行执行更新，每个块独立分配序列值
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name => l_task_name,
    sql_stmt => 'DECLARE
                   v_seq NUMBER := :start_id;
                 BEGIN
                   FOR rec IN (SELECT ROWID rid FROM PARTICIPANTS_TABLE WHERE ROWID BETWEEN :start_rowid AND :end_rowid) LOOP
                     UPDATE PARTICIPANTS_TABLE SET COLUMN_ID = v_seq WHERE ROWID = rec.rid;
                     v_seq := v_seq + 1;
                   END LOOP;
                 END;',
    language_flag => DBMS_SQL.NATIVE,
    parallel_level => 16
  );
  -- 清理任务
  DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task_name);
END;
/

方案3：直接路径INSERT批量导入 #

若允许导出原表数据，可通过直接路径INSERT结合序列生成自增列：

1. 创建大缓存序列：

CREATE SEQUENCE SEQ_PARTICIPANTS
MINVALUE 1
MAXVALUE 400000000
INCREMENT BY 1
START WITH 1
CACHE 100000; -- 增大缓存减少序列调用开销

2. 创建空表并添加column_id列：

CREATE TABLE PARTICIPANTS_TABLE_NEW
AS SELECT * FROM PARTICIPANTS_TABLE WHERE 1=0;
ALTER TABLE PARTICIPANTS_TABLE_NEW ADD COLUMN_ID INTEGER;
ALTER TABLE PARTICIPANTS_TABLE_NEW PARALLEL 16;
ALTER TABLE PARTICIPANTS_TABLE_NEW NOLOGGING;

3. 并行直接路径插入数据：

INSERT /*+ APPEND PARALLEL(16) */ INTO PARTICIPANTS_TABLE_NEW
SELECT SEQ_PARTICIPANTS.NEXTVAL, t.*
FROM PARTICIPANTS_TABLE t;
COMMIT;

关键优化点 #

• 并行度匹配硬件：并行度设置为服务器CPU核心数的1-2倍，避免资源过载
• 日志控制：操作前设置表为NOLOGGING，减少REDO日志生成（需提前备份）
• 避免排序：使用ROW_NUMBER() OVER (ORDER BY NULL)跳过不必要的排序步骤
• 临时禁用约束：操作前禁用非主键约束和索引，完成后再重建，降低维护开销

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