JavaScriptが無効になっています。
この状態では一部の情報が表示されず、すべての機能を利用することができません。
外部表 (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 ;
単一カラムに取り込む外部表
テキストファイルをそのまま1レコード1カラムで取り込む例
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 を使用する。
他のクライアントに対してパフォーマンス上で影響を与える可能性が高い。
外部表 関連事項
Oracle には別の意味の外部表(Outer Table)と呼ばれているものがある。
ネステッドループ結合 における、内部表(Inner Table)と外部表(駆動表とも呼ばれる)の関係のもの。英語では異なる表記(External, Outer)
になっているが日本語訳では同一名称を使用しているので注意。