場景背景
專案需導入 3000 萬條 POI 點位資料至 Oracle，常規 INSERT 耗時數小時，採用 SQL*Loader (sqlldr) 直接路徑載入 僅耗時 20 分鐘，效能提升 90%+。
核心配置檔案
1. 控制檔 input.ctl
LOAD DATA
CHARACTERSET UTF8
INFILE 'H:\POI\baidu.txt'
APPEND
INTO TABLE tbl_poi_baidu
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
POI_NAME,
CLASS_ALL,
ADDR,
PHONE,
TEL,
LONGITUDE,
LATITUDE,
CITY,
CITYCODE,
PROVINCE,
DISTRICT,
STREET,
STREENUMBER,
CLASS_1,
CLASS_2,
CLASS_3,
CLASS_4
)
關鍵參數解析
| 參數 | 作用 | 注意事項 |
|---|
CHARACTERSET UTF8 | 指定源檔編碼 | 必須與資料庫 NLS_CHARACTERSET 一致，建議源檔存為 UTF-8 無 BOM |
APPEND | 追加模式 | 保留既有資料；REPLACE 會截斷表 |
FIELDS TERMINATED BY "," | 欄位分隔符 | 支援多字符，如 '||' |
OPTIONALLY ENCLOSED BY '"'" | 引號包裹 | 處理含分隔符的欄位值 |
TRAILING NULLCOLS | 尾部缺失欄位視為 NULL | 避免行尾少欄位報錯 |
2. 執行命令
sqlldr userid=sde/sde control=input.ctl log=log.out discard=discard.txt errors=1000000
| 參數 | 說明 |
|---|
log=log.out | 詳細日誌，含載入統計、錯誤記錄 |
discard=discard.txt | 不符合 WHEN 條件的記錄輸出檔 |
errors=1000000 | 允許錯誤上限，防少量髒資料中斷整批 |
效能優化關鍵點
- 直接路徑載入 (DIRECT=TRUE)：繞過 SQL 引擎直接寫入資料塊，減少 Redo/Undo 產生
sqlldr ... DIRECT=TRUE
- 禁用索引/約束/觸發器：載入前
DISABLE，載入後 REBUILD 索引、ENABLE 約束
- 調大綁定陣列：
ROWS=100000 減少網路往返
- 並行載入：大檔案拆分多片，多會話併發
PARALLEL=TRUE（需表分區支援）
- 檔案預處理：DOS 合併多檔
copy *.csv all.txt，避免多次調用 sqlldr
中文亂碼終極解決方案
# Linux/Unix 環境
export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
# Windows CMD
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
# 控制檔同步指定
CHARACTERSET AL32UTF8 # 或 UTF8，視資料庫版本而定
常見錯誤與對策
| 錯誤 | 原因 | 解決 |
|---|
| LRM-00112: 多重邏輯檔名 | INFILE 路徑含空格未加引號 | 路徑加雙引號 INFILE '"D:\My Data\file.txt"' |
| ORA-01722: 無效數字 | 數值欄位含非數字字符 | 控制檔加 TO_NUMBER(:col DEFAULT NULL ON CONVERSION_ERROR) 或預清洗 |
| ORA-01401: 插入值過大 | 字串超過欄位長度 | 擴大目標欄位或 SUBSTR 截斷 |
| SQL*Loader-510: 實體檔案無法打開 | 路徑錯誤/權限不足 | 使用絕對路徑、確認服務帳號讀取權限 |
總結
sqlldr 直載模式配合 DIRECT=TRUE、ROWS 大陣列、索引延遲建立，是 Oracle 千萬級離線載入的**性價比之王**。生產環境建議先小批量驗證控制檔語法，再全量執行，並保留 log/bad/discard 三檔案供審計追溯。
