SQL*Loader 大量資料載入實戰：控制檔、直接路徑、平行載入與效能調優

SQL*Loader 架構

• sqlldr (Client) 讀取資料檔 → 解析控制檔 → 載入資料庫
• 兩種路徑：
  • Conventional Path：經 SQL 層 (INSERT)、產生 Redo、觸發器啟動
  • Direct Path：繞過 SQL 層、直接格式化資料區塊寫入、最小 Redo、不啟動觸發器

控制檔語法完整指南

LOAD DATA
INFILE 'data.csv' "str '\r\n'"          -- 資料檔與記錄分隔符
BADFILE 'data.bad'                      -- 錯誤記錄
DISCARDFILE 'data.dsc'                  -- 不符合條件記錄
DISCARDMAX 1000                         -- 最大捨棄筆數
INSERT                                  -- 載入模式: INSERT/APPEND/REPLACE/TRUNCATE
INTO TABLE target_table
WHEN (1:1) <> 'H'                       -- 記錄篩選條件 (跳過標題列)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  -- 欄位分隔
TRAILING NULLCOLS                       -- 尾部缺失欄位視為 NULL
(
  col1  CHAR(10)   "UPPER(:col1)",      -- 欄位定義 + SQL 運算式
  col2  DATE     "TO_DATE(:col2,'YYYY-MM-DD')",
  col3  INTEGER EXTERNAL(5),
  col4  DECIMAL EXTERNAL(10,2),
  col5  POSITION(50:60) CHAR(11)        -- 固定寬度定位
)

實戰範例：CSV 載入

資料檔 (data.csv)

1,Default Web Site,192.168.2.254:80:,RUNNING
2,Other,192.168.2.254:80:test.com,STOPPED
3,Third,192.168.2.254:81:thirdabc.com,RUNNING

控制檔 (load.ctl)

LOAD DATA
INFILE 'data.csv'
BADFILE 'data.bad'
DISCARDFILE 'data.dsc'
APPEND
INTO TABLE resultxt
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
  resultid  CHAR(10),
  website   CHAR(50),
  ipport    CHAR(50),
  status    CHAR(20)
)

執行命令

sqlldr userid=fancy/testpass control=load.ctl log=load.log
  DIRECT=TRUE PARALLEL=TRUE ERRORS=10000
  READSIZE=1048576 BINDSIZE=10485760

Direct Path 載入關鍵參數

平行直接路徑載入架構

-- 1. 表需分區
CREATE TABLE sales (...) PARTITION BY RANGE (sale_date) (...);

-- 2. 多個資料檔對應分區
sqlldr ... DIRECT=TRUE PARALLEL=TRUE FILE=data_p1.dat ...
sqlldr ... DIRECT=TRUE PARALLEL=TRUE FILE=data_p2.dat ...
-- 每個進程載入不同分區，無鎖衝突

效能調優清單

1. 預排序資料：依索引鍵排序，減少索引維護
2. 停用索引/約束：載入後重建，避免逐筆維護
3. 大量提交：ROWS 設大值、COMMIT=Y (Conventional)
4. NOLOGGING：Direct Path 隱含最小 Redo，表空間 NOLOGGING 更極致
5. 多檔案平行：CPU 核心數匹配平行度
6. 預先分配空間：ALTER TABLE ... ALLOCATE EXTENT 避免擴展等待

常見問題排查