萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle SQL*Loader 完整實務指南:控制檔、直接路徑載入與效能優化

前言

SQL*Loader(sqlldr)是 Oracle 官方提供的高效能大量資料載入工具,廣泛應用於資料倉庫初始化、ETL 階段性載入、跨平台遷移等場景。相較於傳統 INSERT 逐筆寫入,SQL*Loader 透過直接路徑載入可繞過 SGA、最小化 Redo/Undo 產生,效能提升可達數十倍。本文系統整理 SQL*Loader 核心架構、控制檔寫法、直接路徑模式原理與限制、常用參數與實務優化技巧。

一、SQL*Loader 核心架構與檔案角色

SQL*Loader 架構示意圖

檔案類型說明必要性
控制檔定義資料格式、欄位對應、載入邏輯(WHEN 條件)、目標表、資料檔路徑等。最關鍵檔案。必要
參數檔將命令列參數(userid、control、log、bad、discard 等)集中管理,便於維護與排程。選用
資料檔來源平面檔(CSV、固定寬度、分隔符號等),可為多檔案。必要
錯誤檔載入失敗的記錄(資料型別不符、違反約束、格式錯誤等),副檔名慣用 .bad。自動產生
捨棄檔不符合控制檔 WHEN 條件被過濾的記錄,副檔名慣用 .dsc。選用
日誌檔記錄載入過程統計:讀取/載入/拒絕/捨棄筆數、錯誤訊息、耗時等,副檔名慣用 .log。自動產生

二、控制檔核心語法要素

LOAD DATA
INFILE 'data.csv' "str '|\n'"          -- 資料檔與記錄分隔符
APPEND                                 -- 載入模式:APPEND/INSERT/REPLACE/TRUNCATE
INTO TABLE target_table                -- 目標表
FIELDS TERMINATED BY ','               -- 欄位分隔符
OPTIONALLY ENCLOSED BY '"'             -- 選擇性引號包裹
TRAILING NULLCOLS                      -- 尾端缺漏欄位視為 NULL
(
    col1 CHAR(10),
    col2 DATE 'YYYY-MM-DD',
    col3 DECIMAL EXTERNAL(10,2),
    col4 "UPPER(:col4)",               -- SQL 運算式轉換
    col5 CONSTANT 'DEFAULT_VAL'        -- 固定值
)

進階功能:WHEN 條件過濾、INTO TABLE 多表載入、BINDSIZE/READSIZE 調整緩衝區、DATE_CACHE 加速日期轉換。

三、直接路徑載入深度解析

直接路徑 vs 傳統路徑對比

3.1 兩種載入模式對比

特性傳統路徑直接路徑
機制透過 SQL 層、SGA Buffer Cache、產生完整 Redo/Undo直接格式化資料區塊寫入 Segment 高水位線以上,繞過 SGA
Redo/Undo 量完整產生極少(僅 Data Dictionary Undo,無資料區塊 Undo)
索引維護逐筆維護載入完成後一次重建(可 SORTED INDEXES 優化)
觸發器/約束全部觸發/檢查僅支援 NOT NULL/UNIQUE/PK/CHECK;FK/Trigger 需停用或改用傳統路徑
並行度單一 Session 序列化支援 PARALLEL=TRUE 多 Session 並行載入不同分區
適用場景小量資料、需觸發器/複雜約束、頻繁小批次大量資料初始載入、分區表並行載入、資料倉庫批次作業

3.2 啟用直接路徑

  • 命令列參數:DIRECT=TRUE
  • 控制檔選項:OPTIONS (DIRECT=TRUE)

3.3 直接路徑的關鍵限制(必讀)

  1. 同一表同一時間僅能有一個直接路徑載入:不適合小量頻繁載入,專為大批量設計。
  2. 高水位線以下的閒置空間不會被重用:載入後段大小單調增長,若後續大量刪除需考慮 ALTER TABLE ... SHRINK SPACE 或重組。
  3. 同一 Session 載入期間不可對該表執行任何操作(含 SELECT),會報 ORA-12838。
  4. 僅支援特定語句/介面:INSERT /*+ APPEND */ ... SELECT ...、MERGE、OCI Direct Path API、SQL*Loader DIRECT=TRUE。
  5. 不支援:Cluster Table、Object Table、含 LOB/FILE/XMLType 欄位(11g+ 部分支援)、啟用 FK/Trigger 的表(需先停用)。

四、實務效能優化清單

  1. 調大 BINDSIZE/READSIZE:預設 256KB/1MB,大檔案建議設 10MB~100MB(BINDSIZE=10000000 READSIZE=10000000)。
  2. 啟用 DATE_CACHE:大量日期欄位轉換時,DATE_CACHE=10000 可大幅減少轉換開銷。
  3. 分區表並行載入:PARALLEL=TRUE 搭配多個 Loader Session 分別載入不同分區,線性縮短載入時間。
  4. 預先停用索引/約束:直接路徑載入前 ALTER INDEX ... UNUSABLE、ALTER TABLE ... DISABLE CONSTRAINT,載入完成後 REBUILD/ENABLE,避免逐筆維護開銷。
  5. 使用無記錄模式:ALTER TABLE ... NOLOGGING 搭配直接路徑,進一步減少 Redo(需配合備份策略,因不可恢復)。
  6. 檔案預處理:外部表或 Shell 腳本預先清洗資料(移除 BOM、統一換行符、編碼轉換),減少 Bad File 比例。

五、常見錯誤與排查

錯誤現象可能原因解決方向
ORA-01652: unable to extend temp segment直接路徑排序索引時暫存表空間不足擴充 TEMP 表空間;或分批載入、調小 SORT_AREA_SIZE
ORA-01722: invalid number數值欄位含非數字字元檢查資料檔;控制檔加 NULLIF/DECODE 清洗
ORA-00001: unique constraint violated直接路徑載入違反 PK/UK預先停用約束,載入後驗證再啟用
載入速度未提升未真正啟用直接路徑、或表有 Trigger/FK確認 Log 顯示 "Direct path load";停用 Trigger/FK

六、結語

SQL*Loader 是 Oracle 大量資料載入的基石工具。掌握控制檔寫法、理解直接路徑的原理與限制、善用並行與參數調校,能讓 TB 級資料載入從「數小時」壓縮至「數分鐘」。建議將常用載入模板參數化、腳本化,納入 ETL 標準作業流程,並配合 EXTERNAL TABLE、DBMS_CLOUD(雲端環境)等新技術彈性選擇最佳載入路徑。

copyright © 萬盛學電腦網 all rights reserved