DB / Oracle / 性能诊断

Oracle 数据加载优化

2026.09.06

Oracle 数据加载优化

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

数据加载是 ETL 核心[1]：

方式：

  • SQL*Loader
  • Data Pump
  • External Table
  • INSERT /*+ APPEND */
  • Direct Path

2. INSERT /*+ APPEND */

2.1 直接路径

INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;
COMMIT;

2.2 优势

  • 不写 Undo
  • 少 Redo
  • 直接写数据文件
  • 高速

2.3 限制

  • 表锁
  • 必须 COMMIT 后查询
  • 索引需重建

2.4 NOLOGGING

ALTER TABLE target_table NOLOGGING;

INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;

ALTER TABLE target_table LOGGING;

3. 并行加载

3.1 启用并行 DML

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(t 8) APPEND */ INTO target_table t
SELECT /*+ PARALLEL(s 8) */ * FROM source_table s;

COMMIT;

3.2 多会话

-- 不同范围并行
INSERT INTO target SELECT * FROM source WHERE id BETWEEN 1 AND 1000000;
INSERT INTO target SELECT * FROM source WHERE id BETWEEN 1000001 AND 2000000;

4. SQL*Loader

4.1 直接路径

OPTIONS (DIRECT=TRUE, ERRORS=1000, BINDSIZE=20971520, READSIZE=20971520)
LOAD DATA
  INFILE 'data.csv'
  BADFILE 'bad.bad'
  DISCARDFILE 'discard.dsc'
  APPEND
  INTO TABLE target_table
  FIELDS TERMINATED BY ','
  (id, name, salary)

4.2 并行

OPTIONS (DIRECT=TRUE, PARALLEL=TRUE)
sqlldr userid=scott/tiger control=load.ctl parallel=true

4.3 UNRECOVERABLE

UNRECOVERABLE
LOAD DATA ...

5. External Table

5.1 创建

CREATE TABLE ext_data (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
  )
  LOCATION ('data.csv')
);

5.2 并行加载

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(t 8) APPEND */ INTO target_table t
SELECT /*+ PARALLEL(e 8) */ * FROM ext_data e;

COMMIT;

5.3 优势

  • 灵活
  • 并行
  • 监控

6. Data Pump

6.1 导入

impdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=data.dmp 
  TABLES=source REMAP_TABLE=source:target
  PARALLEL=8 TABLE_EXISTS_ACTION=APPEND

6.2 网络导入

impdp scott/tiger DIRECTORY=dpump_dir 
  NETWORK_LINK=src_link TABLES=source 
  PARALLEL=8

详细见：Oracle Data Pump 详解。

7. 加载前准备

7.1 禁用约束

ALTER TABLE target DISABLE CONSTRAINT fk_...;
ALTER TABLE target DISABLE CONSTRAINT chk_...;
ALTER TABLE target DISABLE ALL TRIGGERS;

7.2 禁用索引

ALTER INDEX idx_name UNUSABLE;
-- 或
ALTER INDEX idx_name INVISIBLE;

7.3 NOLOGGING

ALTER TABLE target NOLOGGING;
ALTER INDEX idx_name NOLOGGING;

8. 加载后处理

8.1 重建索引

ALTER INDEX idx_name REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX idx_name LOGGING NOPARALLEL;

8.2 启用约束

ALTER TABLE target ENABLE CONSTRAINT fk_...;
ALTER TABLE target ENABLE ALL TRIGGERS;

8.3 收集统计

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TARGET', cascade => TRUE);

8.4 备份

-- 加载后立即备份
RMAN> BACKUP TABLESPACE users;

9. 性能对比

方式速度备注
普通 INSERT慢写 Undo/Redo
APPEND快直接路径
APPEND + NOLOGGING很快少 Redo
APPEND + PARALLEL最快并行
SQL*Loader DIRECT很快经典
External Table快灵活

10. 应用场景

10.1 数据仓库 ETL

-- 每日加载
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(t 8) APPEND */ INTO sales_fact t
SELECT /*+ PARALLEL(s 8) */ * FROM ext_sales s 
WHERE sale_date = TRUNC(SYSDATE - 1);

COMMIT;

10.2 数据迁移

-- 大表迁移
ALTER TABLE target NOLOGGING;

INSERT /*+ APPEND */ INTO target 
SELECT * FROM source@old_db;

ALTER TABLE target LOGGING;

10.3 临时表加载

-- 中间结果
INSERT /*+ APPEND */ INTO temp_result
SELECT ... FROM big_table WHERE ...;
COMMIT;

11. 常见坑与排错

11.1 ORA-12838: 不能读取/修改

-- APPEND 后未 COMMIT
INSERT /*+ APPEND */ INTO t SELECT ...;
SELECT * FROM t;  -- 错误
COMMIT;
SELECT * FROM t;  -- 正确

11.2 索引失效

-- APPEND 后索引 UNUSABLE
ALTER INDEX idx_name REBUILD;

11.3 性能慢

-- 1. NOLOGGING
-- 2. 并行
-- 3. 禁用约束/索引
-- 4. 增大 PGA

12. 最佳实践

  1. APPEND 直接路径：性能
  2. NOLOGGING：少 Redo
  3. PARALLEL：并行
  4. 禁用约束/索引：加载快
  5. 加载后重建索引：恢复
  6. 收集统计：CBO
  7. 立即备份：保护
  8. COMMIT 后查询：避免错误
  9. External Table 灵活：ETL
  10. 测试验证：性能

13. 参考资料

[1] Oracle Database Utilities 19c, “SQL*Loader” https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/