Oracle 大數據量 DML 優化實戰：索引暫停、批量提交、臨時表空間、NOLOGGING、CTAS、Parallel、PCTFREE 六大技法

前言

面對百萬/億級數據的 UPDATE/INSERT/DELETE，單條 SQL 往往因 Redo 暴增、Undo 爆滿、索引維護開銷大而極慢甚至失敗。本文系統梳理六大優化技法：索引策略、批量提交、臨時表空間、NOLOGGING、CTAS/直路徑、Parallel、PCTFREE，並給出適用場景判斷矩陣。

技法一：索引暫停/重建 (針對 UPDATE 索引列)

-- 1. 禁用/刪除受影響索引
ALTER INDEX idx_emp_deptno UNUSABLE;
-- 或 DROP INDEX idx_emp_deptno;

-- 2. 執行大量 UPDATE
UPDATE emp SET deptno = 20 WHERE deptno = 10;

-- 3. 重建索引
ALTER INDEX idx_emp_deptno REBUILD ONLINE;

權衡：若 UPDATE 列有索引，維護開銷大；但索引加速 WHERE 定位。實測比較：「有索引 UPDATE」vs「無索引全表掃描 UPDATE + 重建」。大多數全表大比例更新時，暫停索引更優。

技法二：批量提交 (控制 Undo/臨時空間)

DECLARE
  v_cnt NUMBER := 0;
BEGIN
  FOR r IN (SELECT rowid rid FROM big_table WHERE process_flag = 'N') LOOP
    UPDATE big_table SET process_flag = 'Y', process_time = SYSDATE WHERE rowid = r.rid;
    v_cnt := v_cnt + 1;
    IF MOD(v_cnt, 5000) = 0 THEN
      COMMIT;  -- 每 5000 行提交
    END IF;
  END LOOP;
  COMMIT;
END;

要點：避免單事務撐爆 Undo Tablespace、減少鎖持有時間、降低 ORA-01555 風險。提交頻率 1000-10000 視 Undo 大小調整。

技法三：專用大臨時表空間 (應對排序/哈希/中間結果)

CREATE TEMPORARY TABLESPACE big_temp
TEMPFILE '/u01/app/oracle/oradata/big_temp01.dbf'
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 會話級指定
ALTER SESSION SET TEMPORARY TABLESPACE big_temp;

隔離大作業排序空間，不影響業務臨時表空間。

技法四：NOLOGGING + 直路徑操作 (極速減 Redo)

-- 表級 NOLOGGING
ALTER TABLE big_table NOLOGGING;

-- 直路徑插入 (APPEND)
INSERT /*+ APPEND */ INTO big_table SELECT * FROM source_table;
COMMIT;

-- 或 CTAS (隱含直路徑+NOLOGGING)
CREATE TABLE big_table_new NOLOGGING PARALLEL 4 AS
SELECT * FROM source_table;

效果：最小化 Redo 生成 (僅記錄元數據)、繞過緩沖區直接寫數據文件、性能提升 5-10 倍。風險：不可恢復 (需備份後立即執行)、Data Guard 同步中斷 (需 FORCE LOGGING 或備份後切換)。

技法五：CTAS (Create Table As Select) + Parallel 重建大表

-- 重建表並去碎片、壓縮、並行
CREATE TABLE big_table_new
NOLOGGING
PARALLEL (DEGREE 8)
COMPRESS FOR QUERY HIGH
AS
SELECT /*+ PARALLEL(8) */ * FROM big_table
WHERE create_date > SYSDATE - 365;

-- 切換
ALTER TABLE big_table RENAME TO big_table_old;
ALTER TABLE big_table_new RENAME TO big_table;
-- 重建索引/授權/同義詞

適用於大比例刪除/更新、表碎片整理、分區轉換。

技法六：調大 PCTFREE (預留更新空間，減少行遷移)

-- 建表時
CREATE TABLE high_update_table (
  id NUMBER PRIMARY KEY,
  data VARCHAR2(2000)
) PCTFREE 30;  -- 默認 10%，高更新表建議 20-40%

-- 既有表調整 (需重建)
ALTER TABLE high_update_table MOVE PCTFREE 30;

原理：塊中預留更多自由空間，UPDATE 導致行長度增長時可原地擴展，避免行遷移 (Row Migration) 產生額外 I/O。

綜合決策矩陣

參數級優化 (會話級)

ALTER SESSION SET SORT_AREA_SIZE = 100000000;  -- 100MB 排序區
ALTER SESSION SET HASH_AREA_SIZE = 100000000;  -- 哈希連接區
ALTER SESSION ENABLE PARALLEL DML;  -- 允許 PDML

總結

「參數調優只能提升 15%，SQL 改寫才是核心」。大數據 DML 優化本質是 減 Redo、控 Undo、避索引維護、利用直路徑/並行。根據業務場景組合上述六大技法，並建立「備份前 → 執行 → 驗證 → 備份後」標準化作業流程，才能在生產環境安全、高效完成大數據操作。