通用SQL优化经典等价改写【三】——插入提速

来源：这里教程网时间：2026-03-03 21:54:43 作者：

往期回顾：

SQL优化经典等价改写1——case when 改造

SQL优化经典等价改写2——rownum分页

大规模数据插入是数据库管理中的常见需求，选择合适的插入策略可以显著提升数据加载效率。本文将通过实际测试对比分析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;

性能优化高级技巧

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

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

预分配空间：使用ALTER TABLE … ALLOCATE EXTENT预分配表空间，减少动态分配开销
禁用约束和触发器：加载期间暂时禁用约束和触发器，完成后重新启用并验证
表分区预处理：对分区表使用交换分区技术加速数据加载

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