Oracle超10亿条数据分批插入优化方案咨询

1. 直接路径插入（Direct Path Insert）+ NOLOGGING #

• 核心逻辑：绕过SGA缓冲区直接写入数据文件，配合NOLOGGING大幅减少重做日志生成，是大数据量插入的首选优化手段
• 代码示例：

  -- 单批全量插入（适合无内存/IO瓶颈的场景）
  INSERT /*+ APPEND */ INTO tbl_2 SELECT * FROM tbl_1;
  COMMIT;
  
  -- ROWID分段分批插入（替代游标，效率更高）
  DECLARE
      v_start_rowid ROWID;
      v_end_rowid ROWID;
      v_batch_size NUMBER := 1000000;
  BEGIN
      SELECT MIN(ROWID), MAX(ROWID) INTO v_start_rowid, v_end_rowid FROM tbl_1;
      WHILE v_start_rowid <= v_end_rowid LOOP
          -- 获取当前批次的结束ROWID
          SELECT ROWID INTO v_end_rowid FROM (
              SELECT ROWID FROM tbl_1 WHERE ROWID >= v_start_rowid
              ORDER BY ROWID
          ) WHERE ROWNUM <= v_batch_size;
          
          INSERT /*+ APPEND NOLOGGING */ INTO tbl_2
          SELECT * FROM tbl_1 WHERE ROWID BETWEEN v_start_rowid AND v_end_rowid;
          
          COMMIT;
          v_start_rowid := v_end_rowid;
      END LOOP;
  END;
  /
  

• 注意：NOLOGGING模式下，若发生介质故障，迁移数据无法恢复，需在完成后立即执行全量备份；APPEND模式会在表尾追加数据，不重用空闲空间。

2. 并行执行（Parallel Execution） #

• 利用多核服务器资源，将查询与插入任务拆分给多个进程并行处理，显著提升吞吐量
• 代码示例：

  -- 开启并行查询与插入，并行度建议设为CPU核心数的1-2倍
  INSERT /*+ APPEND PARALLEL(tbl_2 8) PARALLEL(tbl_1 8) NOLOGGING */
  INTO tbl_2 SELECT * FROM tbl_1;
  COMMIT;
  

• 前置操作：确保源表与目标表已开启并行属性：

  ALTER TABLE tbl_1 PARALLEL 8;
  ALTER TABLE tbl_2 PARALLEL 8;
  

3. 分区表交换（Exchange Partition）（源表为分区表时） #

• 这是效率最高的迁移方案之一，仅修改元数据，不移动实际数据，几乎瞬间完成
• 操作步骤：
  1. 确保tbl_2与tbl_1的表结构、分区规则完全一致
  2. 逐个交换分区：

     ALTER TABLE tbl_2 EXCHANGE PARTITION p202401 WITH TABLE tbl_1 PARTITION p202401;
     

4. 禁用约束与索引，事后重建 #

• 插入过程中维护索引、检查约束会消耗大量资源，建议先禁用，完成后批量重建
• 操作示例：

  -- 禁用目标表的非主键约束与索引
  ALTER TABLE tbl_2 DISABLE CONSTRAINT fk_tbl_2_ref;
  ALTER INDEX idx_tbl_2_col1 DISABLE;
  
  -- 执行插入操作...
  
  -- 重建约束与索引（并行重建提升速度）
  ALTER TABLE tbl_2 ENABLE CONSTRAINT fk_tbl_2_ref;
  ALTER INDEX idx_tbl_2_col1 REBUILD PARALLEL 8;
  

5. 使用Oracle Data Pump（EXPDP/IMPDP） #

• Oracle官方批量迁移工具，性能远优于普通SQL插入，支持并行、NOLOGGING、数据过滤等功能
• 命令示例：

  -- 导出源表
  expdp username/password@db tables=tbl_1 dumpfile=tbl1_dump.dmp parallel=8 logfile=exp_tbl1.log
  
  -- 导入并重命名为目标表
  impdp username/password@db dumpfile=tbl1_dump.dmp remap_table=tbl_1:tbl_2 parallel=8 nologging=yes logfile=imp_tbl2.log
  

6. 数据库IO参数优化 #

• 增大重做日志文件大小：减少日志切换频率，避免归档等待（示例将日志调整为4G）

  ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/db/redo04.log') SIZE 4G;
  ALTER DATABASE DROP LOGFILE GROUP 1;
  

• 调整日志缓冲区：增大LOG_BUFFER减少磁盘IO次数（需重启数据库）

  ALTER SYSTEM SET log_buffer=104857600 SCOPE=SPFILE; -- 设置为100M
  

分批提交最佳实践 #

• 批次大小建议：100万-500万条/批，过小的批次会导致提交次数过多，增加事务开销
• 避免游标：用ROWID或主键范围分批，比游标循环效率高数倍
• 实时监控：迁移过程中关注CPU、IO、重做日志生成速度，动态调整批次大小

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