大规模数据插入是数据库管理中的常见需求，选择合适的插入策略可以显著提升数据加载效率。本文将通过实际测试对比分析Oracle数据库中不同插入策略的性能差异，帮助数据库管理员选择最适合自己场景的数据插入方法。

一、插入策略对比表

二、测试环境与数据

我们使用以下环境和数据进行测试：

• 数据库版本：Oracle 19c
• 硬件配置：8核CPU，16GB内存，SSD存储

• 测试数据：1000万行，每行约100字节

-- 准备1000万行测试数据
CREATE TABLE source_data AS 
SELECT ROWNUM id, LPAD('X', 100, 'X') data 
FROM dual CONNECT BY LEVEL <= 10000000;
-- 创建目标表
CREATE TABLE target_normal (id NUMBER, data VARCHAR2(100));

测试环境：Oracle 19c，8核CPU，16GB内存，SSD存储

三、性能测试详解

1. 普通插入 - 基准性能

-- 执行时间: 9.7秒
INSERT INTO target_normal SELECT * FROM source_data;

性能特点：

• 完整记录redo日志，保证数据完全可恢复
• 数据首先加载到缓冲区，再由后台进程写入磁盘
• 主要等待事件：日志文件同步、缓冲区写入
• 产生完整的undo数据用于回滚
• 生成日志量：约210MB

监控输出：

Top 5 Timed Events:                         Avg %Total
~~~~~~~~~~~~~~~~~~                         wait  Call
Event                        Waits    Time (s) (ms)  Time
---------------------------- ----- ---------- ------ ------
log file sync                  325        5.2     16   53.6
db file sequential read        856        2.4      3   24.7
CPU time                                  1.8          18.6
log file parallel write        325        0.2      1    2.1

2. 直接路径插入 - 显著提升

-- 执行时间: 2.8秒
INSERT /*+ APPEND */ INTO target_normal SELECT * FROM source_data;

性能特点：

• 绕过缓冲区，直接写入数据文件
• 减少redo日志记录(只记录元数据变化)
• 减少缓冲区争用和管理开销
• 采用批量处理方式
• 日志生成量减少到约84MB(减少约60%)

监控输出：

Top 5 Timed Events:                         Avg %Total
~~~~~~~~~~~~~~~~~~                         wait  Call
Event                        Waits    Time (s) (ms)  Time
---------------------------- ----- ---------- ------ ------
direct path write             187        1.3      7   46.4
log file sync                  92        0.9     10   32.1
CPU time                                 0.5          17.9
direct path read              125        0.1      1    3.6

3. 并行直接路径 - 多核利用

-- 执行时间: 0.9秒
INSERT /*+ APPEND */ INTO target_normal
SELECT /*+ PARALLEL(8) */ * FROM source_data;

性能特点：

• 利用多线程并发处理数据
• 8个并行进程同时处理不同数据块
• 线性加速，接近理论最大值
• 日志记录与直接路径插入相似
• 适合多核CPU环境和大规模数据

监控输出：

Top 5 Timed Events:                         Avg %Total
~~~~~~~~~~~~~~~~~~                         wait  Call
Event                        Waits    Time (s) (ms)  Time
---------------------------- ----- ---------- ------ ------
PX Deq: Execute Reply         144        0.3      2   33.3
direct path write             352        0.3      1   33.3
CPU time                                 0.2          22.2
PX Deq Credit: send blkd        8        0.1     13   11.1

4. 关闭日志并行插入 - 极限性能

-- 执行时间: 0.5秒
ALTER TABLE target_normal NOLOGGING;
INSERT /*+ APPEND */ INTO target_normal
SELECT /*+ PARALLEL(8) */ * FROM source_data;

性能特点：

• 几乎完全消除日志开销
• 日志生成量减少95%以上，仅约10MB
• 结合并行和直接路径写入的最大性能
• 适合初始加载和可重建的数据
• 注意：数据库崩溃时无法恢复这些数据

监控输出：

Top 5 Timed Events:                         Avg %Total
~~~~~~~~~~~~~~~~~~                         wait  Call
Event                        Waits    Time (s) (ms)  Time
---------------------------- ----- ---------- ------ ------
direct path write             352        0.3      1   60.0
CPU time                                 0.2          40.0

四、关键性能指标对比

五、使用建议

1. 小批量数据插入

推荐策略：普通插入
适用场景：交易数据、关键业务记录
优势：完整事务保护，数据安全性最高
命令示例：INSERT INTO table_name VALUES (…)

2. 中等批量数据插入

推荐策略：直接路径插入
适用场景：每日批量加载，百万级数据
优势：平衡性能与安全性
命令示例：INSERT /*+ APPEND */ INTO table_name SELECT * FROM source

3. 大批量数据插入(多核环境)

推荐策略：并行直接路径插入
适用场景：初始数据加载，报表数据刷新
优势：充分利用多核CPU，提供近线性扩展
命令示例：

INSERT /*+ APPEND */ INTO target
SELECT /*+ PARALLEL(8) */ * FROM source;

4. 极限性能需求(临时/可重建数据)

推荐策略：关闭日志并行直接路径插入
适用场景：临时表、可从源系统重建的数据
优势：提供最大插入性能
注意：灾难恢复时这些数据无法通过日志恢复
命令示例：

ALTER TABLE target NOLOGGING;
INSERT /*+ APPEND */ INTO targetSELECT /*+ PARALLEL(8) */ * FROM source;

性能优化高级技巧

1. 批量提交：大批量插入时控制提交频率，避免频繁的日志同步等待

BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO target SELECT * FROM source WHERE batch_id = i;
    COMMIT;
  END LOOP;
END;

1. 预分配空间：使用ALTER TABLE … ALLOCATE EXTENT预分配表空间，减少动态分配开销

2. 禁用约束和触发器：加载期间暂时禁用约束和触发器，完成后重新启用并验证

3. 表分区预处理：对分区表使用交换分区技术加速数据加载

-- 将数据加载到临时表
INSERT /*+ APPEND PARALLEL(8) */ 
INTO temp_table SELECT * FROM source;-- 交换分区(几乎瞬时完成)
ALTER TABLE main_table EXCHANGE PARTITION part1 WITH TABLE temp_table;

结论

通过选择合适的插入策略，Oracle数据库插入性能可提升近20倍。不同的插入方法在性能、资源消耗和数据安全性方面各有权衡。

根据数据量大小、性能需求和安全要求，灵活选择最适合的插入方式，以获得最佳性能与安全性平衡。特别是对于大规模数据加载，合理使用直接路径插入、并行处理和日志管理技术，可以显著缩短数据加载时间，提高系统整体效率。

对于关键业务数据，始终确保数据安全性优先；对于可重建的临时数据，可以采用更激进的优化策略以获得极限性能。

未完待续...

        SQL优化经典等价改写4——只取所需列

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列