Oracle 9i 引入外部表,允許將平坦文件映射為只讀資料庫表,支援完整 SQL 查詢能力。但外部表 DDL 語法 (ORGANIZATION EXTERNAL) 與 SQL*Loader 控制文件 (.ctl) 差異較大。Oracle 提供隱藏功能:EXTERNAL_TABLE=GENERATE_ONLY 參數,讓 SQL*Loader 自動將 .ctl 轉換為外部表創建腳本,大幅降低開發門檻。
sqlldr userid=scott/tiger control=loademp.ctl EXTERNAL_TABLE=GENERATE_ONLY
-- 輸出寫入 SQL*Loader 日志文件 (loademp.log),包含:
-- 1. CREATE DIRECTORY 對象 (指向數據文件目錄)
-- 2. CREATE TABLE ... ORGANIZATION EXTERNAL (完整外部表 DDL)
-- 3. 示例 INSERT ... SELECT ... FROM 外部表
-- 4. 清理腳本 (DROP TABLE, DROP DIRECTORY)
LOAD DATA
INFILE 'emp_data.txt' "str '|\n'" -- 記錄分隔符 '|\n'
BADFILE 'emp_data.bad'
DISCARDFILE 'emp_data.dsc'
APPEND
INTO TABLE emp_ext
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
empno INTEGER EXTERNAL(4),
ename CHAR(20),
job CHAR(15),
mgr INTEGER EXTERNAL(4),
hiredate DATE "DD-MON-YYYY",
sal DECIMAL EXTERNAL(7,2),
comm DECIMAL EXTERNAL(7,2),
deptno INTEGER EXTERNAL(2)
)
$ sqlldr scott/tiger control=loademp.ctl EXTERNAL_TABLE=GENERATE_ONLY
-- 日志文件 loademp.log 末尾將包含生成的外部表腳本
-- ===== 自動生成腳本 (需人工修飾) =====
-- 步驟 1: 建立目錄對象 (需 CREATE ANY DIRECTORY 權限)
-- 系統生成名稱通常為 SYS_SQLLDR_XT_DIR_00001,建議改為業務名
CREATE OR REPLACE DIRECTORY ext_tab_dir AS '/data/load/emp';
-- 步驟 2: 建立外部表
CREATE TABLE emp_ext (
empno NUMBER(4),
ename VARCHAR2(20),
job VARCHAR2(15),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY '|\n'
BADFILE 'emp_data.bad'
DISCARDFILE 'emp_data.dsc'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(
empno INTEGER EXTERNAL(4),
ename CHAR(20),
job CHAR(15),
mgr INTEGER EXTERNAL(4),
hiredate DATE "DD-MON-YYYY",
sal DECIMAL EXTERNAL(7,2),
comm DECIMAL EXTERNAL(7,2),
deptno INTEGER EXTERNAL(2)
)
)
LOCATION ('emp_data.txt')
)
PARALLEL 4
REJECT LIMIT UNLIMITED;
-- 步驟 3: 查詢/載入示例
INSERT INTO emp_target SELECT * FROM emp_ext;
-- 步驟 4: 清理 (可選)
-- DROP TABLE emp_ext;
-- DROP DIRECTORY ext_tab_dir;
EXT_EMP_DATA_DIR),並確保 OS 路徑存在、Oracle 進程有讀權限。ACCESS PARAMETERS 中添加 CHARACTERSET AL32UTF8 (或具體字符集)。DATE "DD-MON-YYYY") 需與外部表 ACCESS PARAMETERS 中一致。PARALLEL N 與文件數/CPU 核心匹配,大文件建議 4-8。REJECT LIMIT UNLIMITED 允許所有錯誤行進入 BADFILE,不中斷查詢。| 特性 | 外部表 | SQL*Loader (常規路徑) | SQL*Loader (直接路徑 DIRECT=Y) |
|---|---|---|---|
| 查詢能力 | 完整 SQL (JOIN/聚合/分析函數) | 無 | 無 |
| 載入速度 | 中等 (可並行查詢+直路徑插入) | 慢 | 極快 (繞過 SQL 層、UNDO/REDO 極少) |
| 數據轉換 | SQL 函數/CASE/正則 | 控制文件函數 | 有限 |
| 錯誤處理 | 查詢時發現 (BADFILE) | 載入時記錄 | 載入時記錄 |
| 索引/約束 | 查詢時不維護,載入目標表時維護 | 載入時維護 (可禁用) | 載入後重建 |
| 適用場景 | ETL 階段清洗/轉換/驗證、臨時分析、跨系統數據聯邦查詢 | 小批量、簡單載入 | 大數據量初始載入、數據倉庫加載窗口 |
-- 12c+ 分區外部表:按年份分區,每年一個文件
CREATE TABLE sales_ext (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY sales_data_dir
ACCESS PARAMETERS (...)
LOCATION ('sales_2020.csv', 'sales_2021.csv', 'sales_2022.csv')
)
PARTITION BY RANGE (sale_date) (
PARTITION p2020 VALUES LESS THAN (DATE '2021-01-01') LOCATION ('sales_2020.csv'),
PARTITION p2021 VALUES LESS THAN (DATE '2022-01-01') LOCATION ('sales_2021.csv'),
PARTITION p2022 VALUES LESS THAN (DATE '2023-01-01') LOCATION ('sales_2022.csv')
);
-- 混合分區表:歷史分區內部表 (高性能查詢),最新分區外部表 (即時數據)
CREATE TABLE sales_hybrid (...)
PARTITION BY RANGE (sale_date) (
PARTITION p_hist VALUES LESS THAN (DATE '2023-01-01') COMPRESS FOR QUERY HIGH,
PARTITION p_curr VALUES LESS THAN (MAXVALUE) EXTERNAL ... -- 外部表分區
);