續接上集，深入示範 **單一控制檔載入多張表**、`WHEN` 條件分流、`TRAILING NULLCOLS`、`NULLIF`、日期格式轉換、`CONTINUEIF` 合併實體記錄、`DIRECT=TRUE` 直接路徑載入、`BADFILE`/`DISCARDFILE` 錯誤隔離。
進階控制檔範例：多表條件分流載入
LOAD DATA
INFILE 'multi_data.txt'
BADFILE 'multi.bad'
DISCARDFILE 'multi.dsc'
LOG 'multi.log'
-- 表 1：類型 A 記錄
INTO TABLE target_a
WHEN (1:1) = 'A'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
id,
col1,
col2,
created_date DATE 'YYYY-MM-DD'
)
-- 表 2：類型 B 記錄
INTO TABLE target_b
WHEN (1:1) = 'B'
FIELDS TERMINATED BY ','
(
id,
col3,
col4,
amount DECIMAL EXTERNAL
)
-- 表 3：預設其餘
INTO TABLE target_other
FIELDS TERMINATED BY ','
(
id,
raw_line CHAR(4000)
)
關鍵進階子句詳解
| 子句 | 功能 | 範例 |
|---|
| WHEN (pos:pos) = 'val' | 依資料檔固定位置字元決定載入哪個 INTO TABLE | WHEN (1:1) = 'A' |
| FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' | CSV 標準：逗號分隔、雙引號選擇性包圍 (含逗號/換行的欄位) | 標準 CSV |
| NULLIF (col) = 'N/A' | 資料檔欄位值為 'N/A' 載入為 NULL | NULLIF (status) = 'N/A' |
| DATE 'YYYY-MM-DD HH24:MI:SS' | 欄位直接轉換為 DATE 型別，避免後續 TO_DATE | created_date DATE 'YYYY-MM-DD' |
| DECIMAL EXTERNAL | 數字外部格式 (含符號、小數點、科學標記) 自動轉換 | amount DECIMAL EXTERNAL |
| TRAILING NULLCOLS | 資料列尾端缺少欄位視為 NULL (避免「欄位不足」錯誤) | INTO TABLE t ... TRAILING NULLCOLS |
| CONTINUEIF THIS (1:1) = '#' | 邏輯記錄跨多行：若本行第 1 字元為 #，合併下一行 | 續行標記 |
| CONTINUEIF LAST (1:1) = '\\' | 上一行尾字元為 \ 表示續行 | 反斜線續行 |
直接路徑載入 (Direct Path Load) 效能最大化
sqlldr userid=eygle/eygle control=data.ctl DIRECT=TRUE PARALLEL=TRUE
- `DIRECT=TRUE`：繞過 SQL 引擎，直接格式化資料塊寫入資料檔，**速度提升 5-10 倍**。
- `PARALLEL=TRUE`：多執行緒平行載入 (需分區表、多檔案、多 session)。
- 限制：不可有觸發器、外鍵、叢集表、全域索引維護 (需事後 `UNUSABLE` 重建)、不可 `RECOVERABLE` (預設 `NOLOGGING`)。
錯誤隔離與事後分析
- BADFILE：格式錯誤、資料型別轉換失敗、條件約束違反的記錄寫入此檔，**格式同原資料檔**，修正後可重跑。
- DISCARDFILE：不符合任何 `WHEN` 條件、被 `DISCARD` 子句過濾的記錄。
- LOG：載入統計 (讀取/載入/拒絕/捨棄筆數)、錯誤詳情 (ORA-錯誤碼、行號、欄位)、資料檔/控制檔路徑、執行時間。
常見問題排查
| 現象 | 原因與對策 |
|---|
| ORA-01722: invalid number | 數字欄位含非數字字元 → `NULLIF` 處理髒資料、或改 `CHAR` 載入後 `TO_NUMBER` 清洗 |
| ORA-01841: (full) year must be between -4712 and +9999 | 日期格式不符 → 控制檔指定正確 `DATE 'format'`、或載入 `CHAR` 後 `TO_DATE` |
| Field in data file exceeds maximum length | 欄位長度超過表定義 → 擴大表欄位、或控制檔 `CHAR(n)` 截斷 |
| Record discarded: failed all WHEN clauses | 無符合條件記錄 → 檢查 `WHEN` 位置/值、或加預設 `INTO TABLE` 無 WHEN |
| Direct path load fails with triggers | 直接路徑不支援觸發器 → 停用觸發器、或改常規路徑 |
最佳實務清單
- 大量載入**優先評估 Direct Path + PARALLEL**。
- 控制檔、資料檔、Log/Bad/Discard 檔**版控管理** (Git)。
- 載入前**預檢資料檔編碼、行結束符 (LF/CRLF)、欄位計數**。
- 生產環境**先在測試庫全量跑一次**，驗證控制檔、效能、錯誤率。
- 載入後**必跑 `DBCC CHECKDB` 等效驗證** (Oracle: `ANALYZE TABLE ... VALIDATE STRUCTURE`、核對筆數、關鍵欄位抽樣)。
