Oracle 大数据量处理优化

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

---

1. 概述

大数据量处理优化[1]：

场景：

• 数据迁移
• 批量加载
• 报表统计
• 数据归档

---

2. 数据加载优化

2.1 直接路径

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

2.2 NOLOGGING

ALTER TABLE target NOLOGGING;

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

ALTER TABLE target LOGGING;

详细见：Oracle 数据加载优化。

2.3 SQL*Loader

sqlldr userid=scott/tiger control=load.ctl direct=true parallel=true

2.4 External Table

CREATE TABLE ext_data (...) 
ORGANIZATION EXTERNAL (...);

INSERT /*+ APPEND PARALLEL(t 8) */ INTO target
SELECT * FROM ext_data;
COMMIT;

---

3. 数据迁移

3.1 DB Link

-- 1. 小表：直接
INSERT INTO target SELECT * FROM source@remote_db;

-- 2. 大表：并行 + APPEND
INSERT /*+ APPEND PARALLEL(t 8) */ INTO target t
SELECT /*+ PARALLEL(s 8) */ * FROM source@remote_db s;
COMMIT;

3.2 Data Pump

expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=src.dmp TABLES=source PARALLEL=8
impdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=src.dmp TABLES=source REMAP_TABLE=source:target PARALLEL=8

详细见：Oracle Data Pump 详解。

3.3 Transportable Tablespace

-- TTS
ALTER TABLESPACE users READ ONLY;

expdp ... transport_tablespaces=users ...

-- 复制文件
impdp ... transport_datafiles='/u01/oradata/users01.dbf'

ALTER TABLESPACE users READ WRITE;

详细见：Oracle Transportable Tablespace (TTS)。

---

4. 批量更新

4.1 并行 DML

ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL(t 8) */ target t
SET status = 'PROCESSED'
WHERE date_col < SYSDATE - 30;
COMMIT;

详细见：Oracle 并行 DML 与 DDL。

4.2 分批更新

-- 避免长事务
BEGIN
  LOOP
    UPDATE target SET status = 'PROCESSED'
    WHERE date_col < SYSDATE - 30
      AND status = 'PENDING'
      AND ROWNUM <= 10000;
    
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

4.3 MERGE

MERGE /*+ PARALLEL(t 8) */ INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.col = s.col
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

---

5. 批量删除

5.1 分区 TRUNCATE

-- 最快
ALTER TABLE sales TRUNCATE PARTITION p_old UPDATE GLOBAL INDEXES;

5.2 分批 DELETE

BEGIN
  LOOP
    DELETE FROM sales 
    WHERE sale_date < SYSDATE - 365 
      AND ROWNUM <= 10000;
    
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

5.3 CREATE 重建

-- 不删除，重建
CREATE TABLE sales_new 
  NOLOGGING PARALLEL 8
AS SELECT * FROM sales WHERE sale_date >= SYSDATE - 365;

DROP TABLE sales;
RENAME sales_new TO sales;

---

6. 报表统计

6.1 物化视图

CREATE MATERIALIZED VIEW mv_sales_summary
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
AS
SELECT dept_id, region, SUM(amount), COUNT(*)
FROM sales
GROUP BY dept_id, region;

详细见：Oracle 物化视图性能优化。

6.2 In-Memory

ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY HIGH;

SELECT dept_id, SUM(amount) FROM sales GROUP BY dept_id;

详细见：Oracle 12c In-Memory Column Store。

6.3 并行

SELECT /*+ PARALLEL(8) */ 
  dept_id, SUM(amount)
FROM sales
GROUP BY dept_id;

---

7. 数据归档

7.1 分区

-- 按日期分区
CREATE TABLE sales (...) 
PARTITION BY RANGE (sale_date) (
  PARTITION p2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
  PARTITION p2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
  PARTITION p_recent VALUES LESS THAN (MAXVALUE)
);

-- 归档旧分区
ALTER TABLE sales 
  EXCHANGE PARTITION p2020 
  WITH TABLE sales_archive_2020;

7.2 压缩归档

-- HCC 压缩（Exadata）
ALTER TABLE sales_archive MOVE COMPRESS FOR ARCHIVE HIGH;

-- OLTP 压缩
ALTER TABLE sales_archive MOVE COMPRESS FOR OLTP;

详细见：Oracle 表压缩技术。

7.3 Transportable

-- 归档表空间传输
ALTER TABLESPACE archive_tbs READ ONLY;
expdp ... transport_tablespaces=archive_tbs ...

---

8. ETL 流程

8.1 流程

1. 抽取（Extract）
   - 外部表
   - DB Link

2. 加载（Load）
   - APPEND
   - NOLOGGING

3. 转换（Transform）
   - SQL
   - PL/SQL

4. 聚合（Aggregate）
   - 物化视图
   - In-Memory

8.2 优化

-- 1. 并行
-- 2. NOLOGGING
-- 3. APPEND
-- 4. 禁用索引
-- 5. 加载后重建

---

9. 性能对比

9.1 普通 INSERT

1 亿行：60 分钟

9.2 APPEND + NOLOGGING

1 亿行：8 分钟

9.3 PARALLEL + APPEND + NOLOGGING

1 亿行：3 分钟

---

10. 监控

10.1 加载进度

SELECT 
  sid, 
  opname, 
  sofar, 
  totalwork,
  ROUND(sofar / totalwork * 100, 2) AS pct
FROM v$session_longops
WHERE time_remaining > 0;

10.2 并行执行

SELECT * FROM v$px_process;
SELECT * FROM v$px_session;

---

11. 常见坑与排错

11.1 ORA-12838

-- APPEND 后未 COMMIT
COMMIT;

11.2 索引失效

-- 加载后索引 UNUSABLE
ALTER INDEX ... REBUILD;

11.3 临时表空间满

-- 大排序
-- 1. 增大临时表空间
-- 2. 并行
-- 3. 分批

---

12. 最佳实践

1. APPEND + NOLOGGING：性能
2. PARALLEL：并行
3. 禁用索引/约束：加载快
4. 重建索引：完成后
5. 分批操作：避免长事务
6. 分区归档：管理
7. 物化视图：报表
8. In-Memory：分析
9. 监控进度：v$session_longops
10. 立即备份：NOLOGGING 后

---

13. 参考资料

[1] Oracle Database Data Warehousing Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/