Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

外部表 (External Table)

外部ファイルに対して、あたかもテーブルが存在するかのようにアクセスすることができる。マニュアル上では、SQL*Loader 機能を補足する機能と位置付けになっている。

例えば CSV ファイルの定義を CREATE TABLE で宣言するだけでローディング時間ゼロで即座に読み込む（アクセスする）ことができる。

SQL*Loader と大きく異なる点はテーブルとしての実体を持たない点である。(長所であり、短所でもある)
SQL*Loader はデータを、メディアファイルから永続テーブルに取り込む、マテリアライズド・ビュー
外部表(External Table) はファイルのデータをテーブルが存在するように見せる ビュー と考えると、その違いをイメージしやすい。
外部表はデータファイルを変更するとテーブルの検索結果にすぐに反映される。*1
ファイルを表として扱うことで外部表 ⇒ 複雑な変換*2 ⇒ 永続表 という利用方法が効果的。
例: EDI(全銀ファイルの取り込み処理など)やデータウェアハウスシステムなどで定期的に外部サーバーから提供される外部ファイルのインポートとフォーマット変換などは、独自のファイル読み込みプログラムより低コストで汎用性も高く、さらに高速に処理することができる。

(注意点)
ファイル ⇒ アクセスドライバ・メモリ(内部形式に変換) ⇒ テーブルで毎回読み込み操作が行われるために永続表に比べて非常に効率が悪い。(頻繁に参照する表には向いていない)
外部表の特徴を無視した使い方をするとレコード件数が増加したときに満足できるレスポンスが得られなくなる。
LONG 列はサポートされていない。

一方、永続表はキャッシュアルゴリズムにより、2 回目以降のアクセスでは高速化する可能性はあるものの単発のアクセスの場合、キャッシュを無駄に占領して全体のヒット効率を低下させてしまう。
外部表はシーケンシャルアクセスのメディア、SQL*Loader はランダムアクセスのメディアのような特徴があるともいえる。

外部表 〜 準備 動作環境設定( ディレクトリの作成および権限付与 )

読み込み元と書き込み先の作成
作成ユーザーには DBA 権限が必要
サーバープロセス(通常は oracle)がディレクトリに読み書き権限が必要( chown + chmod , etc )

-- data(データファイル用)、logs(log、bad、discファイル用)
-- DBA 権限を持つユーザーで接続
CREATE OR REPLACE DIRECTORY external_data AS '/u04/xxx/yyy/data' ;
CREATE OR REPLACE DIRECTORY external_logs AS '/u04/xxx/yyy/logs' ;
--
-- 実行ユーザー(rivus)へディレクトリ読み書き権限を付与
-- (rivus が DBA 権限を持っていない場合)
GRANT READ  ON DIRECTORY external_data TO rivus ;
GRANT WRITE ON DIRECTORY external_logs TO rivus ;

単一カラムに取り込む外部表

テキストファイルをそのまま１レコード１カラムで取り込む例

CREATE TABLE SIMPLE_FILE (
    TEXT    VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY external_data
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
    )
    LOCATION ('ascii_file.txt')
);

外部表の定義例 〜 簡単な CSV 読み込みの例

external_data ディレクトリ にある sample.csv ファイルから二重引用符を囲み文字、カンマをフィールドの区切り文字、改行をレコード区切り文字とした 外部ファイルを外部表として宣言する。
カラムは数値(KAZU)、文字列(MOJI)、日付(HIZUKE) とする、但し 日付のフォーマットは YYYY/MM/DD。

DROP TABLE SAMPLE;
CREATE TABLE SAMPLE (
    KAZU    NUMBER,
    MOJI    VARCHAR2(5),
    HIZUKE  DATE)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY external_data
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
        (
            KAZU,
            MOJI,
            HIZUKE CHAR DATE_FORMAT DATE MASK 'yyyy/mm/dd'
        )
    )
    LOCATION (
        'sample.csv'
    )
);

sample.csv の例

1,"abc",2005/01/01
2,"abcde",2006/01/01

外部表の定義例 〜 実用的な固定長例

入力ファイルフォーマット

このサンプルでポイントにしている点

• 文字コードが異なる。 (データファイルは ShiftJIS、改行はCR+LF、DB サーバーは EUC)
• レコードデリミタは改行。(改行は 2 バイト:Win系 CR+LF / UNIX系 LFのみ)
• レコード長は固定長。(60 バイト＋改行(CR+LF) = 62 バイト)
• データファイルに不要なデータを含んでいる。(＊ で表現)
• データファイルのフィールド長とデータベースの項目長は一致するとは限らない。
• データファイルの複数フィールドを結合して作成するカラムがある。(姓名)
• データファイルに存在しないカラムがある。(デフォルト値の設定が必要) (備考)
• データファイルは複数ファイル提供される。
• 動作ログや除外されたログなどをファイルに出力する。
• 読み込み操作をパラレル化する。(常に適用されるわけではない。マニュアル参照)

ファイルレイアウト

フィールド名		テキストフォーマット
部門	DEPT	DECIMAL(2)
ユーザーID	USER_ID	CHARACTER(5)
不要なデータ	-	CHARACTER(1)
名前	FIRST_NAME	CHARACTER(10)
名字	LAST_NAME	CHARACTER(10)
靴の大きさ	SHOE_SIZE	NUMERIC(3,1)
雇用日	HIRE_DATE	DATE_TIME_STRING(19)
不要なデータ	-	CHARACTER(9)
行区切り文字		\r\n

イメージ

1 レコード 62 バイト
9(2)	X(5)	X(1)	X(10)	X(10)	9(3,1)	X(19)	X(9)	CR+LF
...	...	...	...	...	...	...	...	...

サンプル

10U1000*ごんべい  ななし    23.51999/12/31 23:58:59*********
10U1001*nonameCxxxnonameDxxx25.52005/12/31 01:02:03*********
10U1002*nonameE   nonameF   35.02005/12/31 01:02:03*********

テーブルレイアウト

カラム名		データタイプ
ユーザーID	USER_ID	VARCHAR2(5)
名前	FIRST_NAME	VARCHAR2(15)
名字	LAST_NAME	VARCHAR2(15)
姓名	FULL_NAME	VARCHAR2(31)
部門	DEPT	NUMBER(2)
靴の大きさ	SHOE_SIZE	NUMBER(3,1)
雇用日	HIRE_DATE	DATE
備考1	HIRE_DATE	VARCHAR2(5)
備考2	MEMO_PAD2	VARCHAR2(5)

外部表の定義例

外部表の定義 ORGANIZATION EXTERNAL 部のチェックは、SELECT しないとわからない。(実行時エラーが発生する) DDL の投入でエラーが発生しなくても正しく定義できているわけではないので注意する。
また、外部表の ORGANIZATION EXTERNAL部には、/* コメント */ を使用すると実行時エラーになる。
行コメント(--)も不可（改善に期待）

DROP TABLE NEW_MEMBER;
CREATE TABLE NEW_MEMBER (
    USER_ID    VARCHAR2(10),
    FIRST_NAME VARCHAR2(15),
    LAST_NAME  VARCHAR2(15),
    FULL_NAME  VARCHAR2(31),
    DEPT       NUMBER(2),
    SHOE_SIZE  NUMBER(3,1),
    HIRE_DATE  DATE,
    MEMO_PAD1  VARCHAR2(5),
    MEMO_PAD2  VARCHAR2(5)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY external_data
    ACCESS PARAMETERS (
        RECORDS FIXED 62
        STRING SIZES ARE IN BYTES
        BADFILE  external_logs:'new_members_%p_%a.bad'
        DISCARDFILE external_logs:'new_members_%p_%a.disc'
        LOGFILE  external_logs:'new_members_%p_%a.log'
        READSIZE 1048576
        DATE_CACHE 1000
        CHARACTERSET JA16SJISTILDE
        FIELDS (
            DEPT        DECIMAL EXTERNAL(2),
            USER_ID     CHAR(5),
            FIRST_NAME  POSITION(*+001) CHAR(10),
            LAST_NAME   CHAR(10),
            SHOE_SIZE   DECIMAL EXTERNAL(4),
            HIRE_DATE   CHAR(19) DATE_FORMAT DATE MASK 'yyyy/mm/dd hh24:mi:ss'
        )
        COLUMN TRANSFORMS (
            FULL_NAME   FROM CONCAT(FIRST_NAME, CONSTANT ' ', LAST_NAME),
            MEMO_PAD1   FROM NULL,
            MEMO_PAD2   FROM CONSTANT 'DUMMY'
        )
    )
    LOCATION (
        external_data:'new_members_1.dat',
        external_data:'new_members_2.dat'
    )
)
PARALLEL 4
REJECT LIMIT UNLIMITED ;

外部表定義に使用する指定子

READSIZE

読み込み用のメモリバッファサイズ(バイト指定) デフォルト 512KB
READSIZE 1048576 (= 1MB)

DATE_CACHE

一度変換した日付をキャッシュしておくサイズ デフォルト 1000
DATE_CACHE 3650 (= 日付単位なら約 10年)
このパラメータの存在からも、日付への変換コストは相対的に他の変換に比べて高い処理と思われる。
(関連項目⇒文字列が日付として正しいか確認する IS_DATE ファンクション )

STRING SIZES ARE IN {BYTES|CHARACTERS}

RECORDS パラメータ
データファイルを BYTE 単位で扱う or 文字単位で扱う(文字列長 CHAR(x) などに影響)

POSITION

FIELDS パラメータ
絶対指定、相対指定がある。
絶対指定には常に固定長(例えば 3桁)の数字で指定すると桁が揃い見やすく、相対指定と区別しやすくなる)

POSITION(003:005)

レコードの先頭の 3U から 5U までの 3U をあらわす。(1 オリジン)

POSITION(*+1:+10)

前の項目から 1U スキップして 10U 分をあらわす。
POSITION(*+1) CHAR(10) でも同じ結果となる。

(注意) U は 便宜勝手に作った単位: バイトか文字のどちらかの単位：STRING SIZES ARE IN 次第

NULLIF、DEFAULTIF

NULLIF、DEFAULTIF を両方しているすると NULLIF が優先順位が高く、NULLIF が FALSE の場合だけに DEFAULTIF が実行される。
条件には等号および不等号のみ使用可能

PARALLEL

ファイルの読み込みのパラレル化(高速化が期待できる:書き込み先のディスクが 1 本では逆効果)
単一ファイルでも、ファイルを分割して読み込みは可能。(分割読み込みさせるためには以下の制限がある)
SKIP (先頭の数行を読み飛ばす定義)を使用すると並列化できない。
日本語環境の場合、可変長レコードの場合は並列化できない。
ユーティリティ・マニュアルの並列化の禁止に関する説明

文字の境界が文字列中の任意のバイトで始まり、
境界を判断できないマルチバイト・キャラクタ・セットのデータ では使用禁止とある。

解読できませんでした(難解な訳です)…原文のマニュアルと日本語文字コードの諸事情から考えると、

ある文字を適当なバイトの位置で取り出した場合でも、それが文字の先頭のバイト
かどうかを判別できるマルチバイト・キャラクタ・セットならば並列化できる。
の事をあらわしているのだと思う。(原文もまわりくどくてうまく直訳できない)

日本語環境で問題を単純化すると UTF-8 は最上位ビットで先頭を判別ができるが、ShiftJIS や EUC では判別できないから可変長レコードのファイルは並列化はできない。

⇒ 参考 UTF-8(wikipedia) 『1バイト目の上位ビットの1の個数でその文字のバイト数が判るようになっている 』

ShiftJIS や EUC だけど 1 ファイルのものを並列化して読み込ませるには…
固定長レコード (RECORDS FIXED) を使用するということになる。
但し、各フィールドが固定長定義でもレコードの区切り文字を使用(RECORDS DELIMITED BY NEWLINE)すると並列化できないと思う。(未検証)

チューニング項目

固定長レコードにする。

RECORDS DELIMITED BY NEWLINE ⇒ RECORDS FIXED integer

固定長フィールドにする。

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ⇒ 使用しない
外部表では SQL*Loader と仕様が異なり単一引用符をクォートに定義するときに
OPTIONALLY ENCLOSED BY '\'' が使用できない。 ENCLOSED BY "'" と二重引用符で囲うようにする。

WHEN、NULLIF、DEFAULTIF の併用や使用は極力控える

共有サーバー接続では使用しない

バッチ処理だけで使用し、共有サーバー接続( OLTP )では使用を控える。
MTS 接続の場合、アクセスドライバが使用するすべてのメモリは、SGA を使用する。
他のクライアントに対してパフォーマンス上で影響を与える可能性が高い。

---

外部表 関連事項

• CSV 出力プロシージャ
• 表関数
• 外部表 (External Table と Outer Table)

Oracle には別の意味の外部表(Outer Table)と呼ばれているものがある。
ネステッドループ結合 における、内部表(Inner Table)と外部表(駆動表とも呼ばれる)の関係のもの。英語では異なる表記(External, Outer) になっているが日本語訳では同一名称を使用しているので注意。