萬盛學電腦網

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

Oracle 9i 外部表生成技巧:用 SQL*Loader EXTERNAL_TABLE=GENERATE_ONLY 自動產生 DDL

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)

完整實戰演示

1. 準備 SQL*Loader 控制文件 (loademp.ctl)

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)
)

2. 執行生成命令

$ sqlldr scott/tiger control=loademp.ctl EXTERNAL_TABLE=GENERATE_ONLY
-- 日志文件 loademp.log 末尾將包含生成的外部表腳本

3. 生成腳本內容解析與修飾

-- ===== 自動生成腳本 (需人工修飾) =====

-- 步驟 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;

關鍵修飾點 (必須人工檢查/調整)

  1. 目錄對象名:系統生成名不可讀,改為業務語義名 (如 EXT_EMP_DATA_DIR),並確保 OS 路徑存在、Oracle 進程有讀權限。
  2. 字符集聲明:若文件非數據庫字符集,需在 ACCESS PARAMETERS 中添加 CHARACTERSET AL32UTF8 (或具體字符集)。
  3. 日期/數字格式模型:SQL*Loader 格式字符串 (如 DATE "DD-MON-YYYY") 需與外部表 ACCESS PARAMETERS 中一致。
  4. 並行度:PARALLEL N 與文件數/CPU 核心匹配,大文件建議 4-8。
  5. 拒絕限制:REJECT LIMIT UNLIMITED 允許所有錯誤行進入 BADFILE,不中斷查詢。
  6. 分區外部表 (12c+):大文件可按範圍/列表分區,支援分區裁剪與並行查詢。

外部表 vs SQL*Loader 選型

特性外部表SQL*Loader (常規路徑)SQL*Loader (直接路徑 DIRECT=Y)
查詢能力完整 SQL (JOIN/聚合/分析函數)無無
載入速度中等 (可並行查詢+直路徑插入)慢極快 (繞過 SQL 層、UNDO/REDO 極少)
數據轉換SQL 函數/CASE/正則控制文件函數有限
錯誤處理查詢時發現 (BADFILE)載入時記錄載入時記錄
索引/約束查詢時不維護,載入目標表時維護載入時維護 (可禁用)載入後重建
適用場景ETL 階段清洗/轉換/驗證、臨時分析、跨系統數據聯邦查詢小批量、簡單載入大數據量初始載入、數據倉庫加載窗口

12c/19c 新特性:分區外部表與混合分區表

-- 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 ...  -- 外部表分區
);
copyright © 萬盛學電腦網 all rights reserved