Oracle SQL*Loader 實戰技巧大全：15 招從入門到精通

前言

SQL*Loader (`sqlldr`) 是 Oracle 高速載入工具，掌握控制檔技巧能大幅提升 ETL 效能與彈性。本文彙整 15 大實戰技巧，附完整控制檔範例。

15 大核心技巧

1. 註解與字符集

-- 控制檔註解用雙連字號
LOAD DATA
CHARACTERSET ZHS16GBK   -- 防止中文亂碼，對應 NLS_LANG
...

2. 自動產生流水號 (RECNUM)

(seqno RECNUM,  -- 載入第幾行資料
 text POSITION(1:1024))

3. 忽略欄位 (FILLER)

FIELDS TERMINATED BY ','
(field1,
 field2 FILLER,  -- 跳過此欄不載入資料表
 field3)

4. 列級過濾 (WHEN)

INTO TABLE my_table
WHEN (1) <> 'H' AND (1) <> 'T' AND (30:37) = '20111217'
(region CONSTANT '31',
 service_key POSITION(1:11) INTEGER EXTERNAL,
 call_b_no POSITION(12:29) CHAR)

5. 跳過標題行 (SKIP)

OPTIONS (SKIP 1)  -- 或命令列 sqlldr ... skip=1

6. 允許尾部欄位為空 (TRAILING NULLCOLS)

FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(deptno, dname "UPPER(:dname)", loc "UPPER(:loc)",
 last_updated DATE 'DD/MM/YYYY',
 entire_line ":deptno||:dname||:loc||:last_updated")

7. 載入時轉換/衍生欄位

(rec_no "my_seq.NEXTVAL",
 region CONSTANT '31',
 time_loaded "TO_CHAR(SYSDATE, 'HH24:MI')",
 data1 POSITION(1:5) ":data1/100",
 data2 POSITION(6:15) "UPPER(:data2)",
 data3 POSITION(16:22) "TO_DATE(:data3, 'YYMMDD')")

8. 合併多行為一行 (CONCATENATE)

CONCATENATE 3  -- 每 3 物理行合併為 1 邏輯行
FIELDS TERMINATED BY ','
(deptno, dname "UPPER(:dname)", loc "UPPER(:loc)", last_updated DATE 'DD/MM/YYYY')

9. 自訂分隔符避免資料衝突

FIELDS TERMINATED BY "|+|"

10. 控制檔內嵌資料 (INFILE *)

LOAD DATA
INFILE *
APPEND INTO TABLE tmp_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(data1, data2)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

11. 多檔案載入同一表

INFILE 'file1.dat'
INFILE 'file2.dat'
INFILE 'file3.dat'
APPEND INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
 ename POSITION(6:15) CHAR,
 deptno POSITION(17:18) CHAR,
 mgr POSITION(20:23) INTEGER EXTERNAL)

12. 單檔案分流載入不同表 (WHEN + FILLER)

INTO TABLE tab1 WHEN tab = 'tab1'
(tab FILLER CHAR(4), col1 INTEGER)
INTO TABLE tab2 WHEN tab = 'tab2'
(tab FILLER POSITION(1:4), col1 INTEGER)
BEGINDATA
tab1|1
tab1|2
tab2|2

13. 同檔案不同區段載入不同表 (位置基礎)

INTO TABLE emp WHEN empno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, ...)
INTO TABLE proj WHEN projno != ' '
(projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL)

14. 壞檔/捨棄檔/日誌檔路徑參數化

sqlldr APS/APS control=AP_CONTRACT.ctl \
  LOG=/log/AP_CONTRACT_${yesterday}.log \
  BAD=/bad/DUE_BILL_${yesterday}.bad \
  DISCARD=/bad/discard_${yesterday}.dis \
  ROWS=10000 READSIZE=20000000 BINDSIZE=20000000

15. 完整生產級控制檔範本

LOAD DATA
INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'
TRUNCATE
INTO TABLE AP_CONTRACT
WHEN (1) <> '1'                    -- 跳過標題行
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
  AGMT_NO            "TRIM(:AGMT_NO)",
  CONTRACT_NO        FILLER,
  LOAN_AMT           "TRIM(:LOAN_AMT)",
  AGMT_HOLDER        "TRIM(:AGMT_HOLDER)",
  LOAN_TYPE_CD       "TRIM(:LOAN_TYPE_CD)",
  CURR_CD            "TRIM(:CURR_CD)",
  BALANCE            "TRIM(:BALANCE)",
  LOAN_DIRC_CD       "TRIM(:LOAN_DIRC_CD)",
  AGMT_START_DATE    "TRIM(:AGMT_START_DATE)",
  AGMT_END_DATE      "TRIM(:AGMT_END_DATE)",
  AGMT_BELONG_ORG_NO "TRIM(:AGMT_BELONG_ORG_NO)",
  MANAGER_NO         "TRIM(:MANAGER_NO)",
  PROCESS_RATE       "TRIM(:PROCESS_RATE)",
  INSURE_METH_TYPE_CD "TRIM(:INSURE_METH_TYPE_CD)",
  AGMT_SIGN_DATE     "TRIM(:AGMT_SIGN_DATE)",
  LOAN_PROP_CD       "TRIM(:LOAN_PROP_CD)",
  LOAN_USE_TYPE      "TRIM(:LOAN_USE_TYPE)",
  ENTRUST_LOAN_FLAG  "TRIM(:ENTRUST_LOAN_FLAG)",
  ENTRUST_NAME       "TRIM(:ENTRUST_NAME)",
  FARM_LOAN_FLAG     "TRIM(:FARM_LOAN_FLAG)",
  FARM_LOAN_TYPE_CD  "TRIM(:FARM_LOAN_TYPE_CD)",
  LOAN_BIZ_TYPE_CD   "TRIM(:LOAN_BIZ_TYPE_CD)",
  ID_TEST            RECNUM,
  CHAR_TEST          CONSTANT '31',
  SQ                 "sqlldr_seq.NEXTVAL",
  TEST_4             "TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS')",
  TEST_5             "TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO)"
)

效能調優三大鐵律

1. DIRECT=TRUE：繞過 SQL 層直接寫入資料塊，速度提升 5-10 倍（需獨佔表、無觸發器/外鍵）。
2. ROWS / READSIZE / BINDSIZE：依記憶體調大，減少 I/O 往返（範例 20MB）。
3. 並行載入：多個 `sqlldr` 同時跑不同分區/表，配合 `PARALLEL=TRUE`。

結語

SQL*Loader 控制檔語法雖顯老舊，但功能極其強大。將上述 15 招內化為肌肉記憶，並建立專案標準控制檔範本（含字符集、錯誤處理、日誌參數化），即可應對 99% 的平檔載入需求。記住：**小資料用 INSERT，大資料用 SQL*Loader，極大資料用 External Table + Parallel DML**。