Oracle SQL*Loader 資料載入完整指南與範例集

前言

SQL*Loader 是 Oracle 高效能資料載入工具，支援變長/定長記錄、多表載入、資料轉換、直接路徑載入等進階功能。

基本用法

sqlldr scott/tiger control=loader.ctl

控制檔範例集

1. CSV 變長欄位（含分隔符與選擇性引號）

LOAD DATA
INFILE 'c:\data\mydata.csv'
INTO TABLE emp
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, empname, sal, deptno)

資料範例：10001,"Scott Tiger",1000,40

2. 定長記錄（POSITION 指定欄位位置）

LOAD DATA
INFILE *
INTO TABLE departments
(dept   POSITION(1:4)   CHAR(4),
 deptname POSITION(6:25) CHAR(20))
BEGINDATA
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE

3. 多表載入（條件導向）

LOAD DATA
INFILE *
INTO TABLE emp WHEN empno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
 ename POSITION(6:15) CHAR,
 deptno POSITION(17:18) CHAR)
INTO TABLE proj WHEN projno != ' '
(projno POSITION(25:27) INTEGER EXTERNAL,
 empno POSITION(1:4) INTEGER EXTERNAL)

4. 載入時資料轉換（運算式）

LOAD DATA
INFILE *
INTO TABLE modified_data
(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')")
BEGINDATA
11111AAAAAAAAAA991201

注意：轉換運算式僅適用常規路徑，DIRECT=TRUE 時無效。

5. 選擇性載入（WHEN 條件）

LOAD DATA
INFILE 'mydata.dat'
BADFILE 'mydata.bad'
DISCARDFILE 'mydata.dis'
INTO TABLE my_selective_table
WHEN (1) <> 'H' AND (1) <> 'T' AND (30:37) = '19991217'
(region CONSTANT '31',
 service_key POSITION(1:11) INTEGER EXTERNAL,
 call_b_no POSITION(12:29) CHAR)

6. 忽略欄位（FILLER，8i+）

LOAD DATA
TRUNCATE INTO TABLE t1
FIELDS TERMINATED BY ','
(field1, field2 FILLER, field3)

7. 多行合併為一記錄

• CONCATENATE n：固定合併 n 筆實體記錄。
• CONTINUEIF condition：條件判斷合併（如第 1 字元為 #）。

效能優化關鍵

1. 載入前 停用索引與約束，載入後重建。
2. 使用 DIRECT=TRUE 直接路徑載入（繞過 SQL 層，寫入資料塊）。
3. 搭配 UNRECOVERABLE 關閉重做日誌（僅限 DIRECT 模式）。
4. 調大 ROWS= 參數減少提交頻率。
5. 平行執行多個 SQL*Loader 作業。

常規 vs 直接路徑

• 常規：透過 INSERT 陳述式，觸發觸發程序、約束檢查、產生重做日誌。
• 直接：繞過 SQL 引擎，直接格式化資料塊寫入，速度快數倍，但限制較多（不支援觸發程序、叢集表、物化視圖等）。

小結

SQL*Loader 功能強大，掌握控制檔語法與效能參數，可滿足從簡單 CSV 匯入到複雜 ETL 轉換的多樣需求。