面對 **80MB 以上** (實務常達 GB/TB 級) 的大量資料移動 —— 表空間調整、歸檔、跨 Schema 複製、測試環境構建 —— 傳統 `INSERT ... SELECT` 配合 COMMIT 速度極慢、產生海量 Redo/Undo、易導致 UNDO 表空間暴增。本文深度解析 **CTAS (Create Table As Select)** 與 **`INSERT /*+ APPEND */`** 兩大**直寫資料檔、繞過 Buffer Cache/Redo** 的極速方案，含適用場景、鎖行為、索引/約束處理與生產級腳本模板。
兩種方案本質相同：**繞過 SGA Buffer Cache、不產生 Redo/Undo (NOLOGGING)、直接在資料檔高水位線 (HWM) 以上格式化新區塊 (Extent) 寫入**。
| 機制 | 傳統 INSERT | CTAS / APPEND |
|---|---|---|
| 路徑 | Conventional Path (Buffer Cache → DBWR) | Direct Path (PGA → Data File) |
| Redo 產生 | 完整 Redo (含 Undo) | **極少** (僅字典變更、空間分配)，NOLOGGING 時幾乎為零 |
| Undo 產生 | 大量 | **幾乎無** |
| 鎖類型 | Row Exclusive (RX) 行級鎖 | **Exclusive (X) 表級鎖 (TM Lock Mode 6)** |
| 並發影響 | 低 (行級鎖) | **高** (獨佔表，DML 會排隊/阻塞) |
| 索引維護 | 同步維護 | **不維護** (新表無索引；APPEND 使索引 UNUSABLE) |
CREATE TABLE target_schema.target_table
TABLESPACE target_tbs
NOLOGGING -- 關鍵：最小化 Redo
PCTFREE 10 PCTUSED 60
STORAGE (INITIAL 5M NEXT 5M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
AS
SELECT * FROM source_schema.source_table
WHERE <過濾條件>; -- 分區剪枝、業務篩選
| 物件 | 是否繼承 | 後續處理 |
|---|---|---|
| 欄位定義、資料類型、NOT NULL 約束 | ✅ 是 | 無 |
| Primary Key、Unique、Foreign Key、Check 約束 | ❌ 否 | **必須手動重建** |
| 預設值 (DEFAULT) | ❌ 否 | 手動 `ALTER TABLE ... MODIFY ... DEFAULT ...` |
| 索引 | ❌ 否 | **建表後平行建立** (`CREATE INDEX ... PARALLEL n`) |
| 分區定義 | ❌ 否 | 需在 CTAS 中指定 `PARTITION BY ...` 或事後 `EXCHANGE PARTITION` |
| Grant/權限 | ❌ 否 | 手動授權 |
-- 1. 建表 (大表建議加 PARALLEL)
CREATE TABLE tgt_schema.big_table
TABLESPACE big_data_tbs NOLOGGING PARALLEL 8
AS SELECT * FROM src_schema.big_table WHERE created_dt >= DATE '2023-01-01';
-- 2. 立即收集統計資訊 (關鍵：CTAS 後無統計資訊)
EXEC DBMS_STATS.GATHER_TABLE_STATS('TGT_SCHEMA', 'BIG_TABLE', DEGREE=>8);
-- 3. 平行建立索引 (排序在 PGA 完成，極快)
CREATE INDEX tgt_schema.idx_big_table_col1 ON tgt_schema.big_table(col1)
TABLESPACE idx_tbs NOLOGGING PARALLEL 8;
-- 重建所有索引腳本
BEGIN
FOR r IN (SELECT index_name FROM user_indexes WHERE table_name = 'BIG_TABLE') LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '||r.index_name||' REBUILD PARALLEL 8 NOLOGGING';
END LOOP;
END;
/
-- 4. 重建約束 (PK 必須先有 Unique Index)
ALTER TABLE tgt_schema.big_table ADD CONSTRAINT pk_big_table PRIMARY KEY (id)
USING INDEX (CREATE UNIQUE INDEX tgt_schema.pk_big_table ON tgt_schema.big_table(id) PARALLEL 8);
-- 5. 同步權限
-- 可從 ALL_TAB_PRIVS 查詢源表權限並動態生成 GRANT 腳本
-- 1. 目標表必須存在且結構相容
-- 2. 執行直寫插入
INSERT /*+ APPEND */ INTO target_schema.target_table
SELECT * FROM source_schema.source_table
WHERE <條件>;
COMMIT; -- 必須提交才能釋放獨佔鎖、使資料可見
-- 執行 APPEND 後，目標表所有索引狀態變為 UNUSABLE
-- 必須重建
ALTER INDEX target_schema.idx_target_table_col REBUILD ONLINE PARALLEL 8;
-- 或事先標記不可用再重建 (11g+)
ALTER INDEX ... UNUSABLE; -- 避免 APPEND 期間維護開銷
INSERT /*+ APPEND */ ...;
ALTER INDEX ... REBUILD;
-- PL/SQL 批次處理中使用
FORALL i IN 1..v_cnt
INSERT /*+ APPEND_VALUES */ INTO target_table VALUES v_rec(i);
-- 適用於中小批次 (千~萬行)，避免單行直寫產生大量小 Extent
| 場景 | 推薦方案 | 核心理由 |
|---|---|---|
| 全新建表、跨表空間/Schema 遷移 | CTAS | 一條 SQL 完成建表+載入、可指定 TABLESPACE/STORAGE/NOLOGGING、最乾淨 |
| 既有表追加歷史資料、定期歸檔 | INSERT /*+ APPEND */ | 保留表結構/權限/索引定義、僅追加資料 |
| 全表替換 (Truncate + Reload) | TRUNCATE + APPEND | TRUNCATE 瞬間釋放空間、重設 HWM、比 DELETE 快萬倍 |
| 需保留目標表索引/約束/權限 | APPEND (配合索引重建) | 避免重建數百個 Grant/同義詞/視圖依賴 |
| OLTP 核心表、不可阻塞寫入 | **禁用兩者** → 改用 RMAN 複製、GoldenGate、或分批小額 COMMIT | 獨佔鎖會凍結業務寫入 |
**CTAS** 是「建新表遷移」的王者，**INSERT /*+ APPEND */** 是「既有表追加」的利器。兩者核心都是 **Direct Path + NOLOGGING**，犧牲並發 (獨佔鎖) 換取極致載入速度與最小 Redo。**生產環境鐵律：維護窗口執行、索引事後平行重建、統計資訊即時收集、備份即時跟進。** 掌握這套組合拳，TB 級資料遷移也能在小時級完成。
