sqlldrとは
sqlldrとはデータベース外にあるファイルをデータベースにロードするツールです。 一般的にはcsvや固定長形式のテキストファイルをロードする場合等によく使われ、 数あるORACLEへのデータインサート方法の中でも最も処理が高速になる可能性が高いツールです。使用方法
sqlldrを使用するためにはcontrolパラメータで指定する制御ファイルと呼ばれるロード元となるファイルやロード先の指定等を記載したファイルを事前に作成しておく必要があります。 以下は制御ファイルのサンプルですが、この設定はE:samp1.datというファイルの中身をtab_samp1というテーブルのcol1とcol2という列に追加(APPEND)ロードする設定です。LOAD DATA INFILE 'E:samp1.dat' INTO TABLE tab_samp1 APPEND FIELDS TERMINATED BY ',' (col1 char(100), col2 integer EXTERNAL)samp1.ctl
制御ファイルを作成後、以下のようにsqlldrコマンドを実行することで実行することができます。
sqlldr USERID=＜ユーザ＞/＜パスワード＞ control=samp1.ctl
例1 可変長テキストファイルをロードする制御ファイル
以下の指定では ダブルクォーテーションで囲まれてカンマで区切られた値を先頭からcol1,col2に追加(APPEND)ロードします。 APPENDをREPLACEやTRUNCATEに変更すると既存の行を削除した後にデータがロードされます。LOAD DATA INFILE 'test.dat' INTO TABLE test APPEND FIELDS TERMINATED BY ',' optionally enclosed by '"' (col1 char(100), col2 integer EXTERNAL)test.ctl
"a0","1111" "c00","2222"test.dat
ロード結果
SQL> select * from test; COL1 COL2 ---------- ---------- a0 1111 c00 2222
例2 固定長のテキストファイルをロードする制御ファイル
以下の指定では先頭から5byte分がcol1のデータ、6バイト目から20byte分がcol2のデータとして扱われます。 基本的には例1のような可変長形式よりも固定長形式のロードのほうが高速で処理することができます。LOAD DATA INFILE 'test.dat' INTO TABLE test APPEND (col1 POSITION(*:5), col2 POSITION(*:20))test.ctl
00001a000000000b000000000 00002c000000000d000000000test.dat
ロード結果
SQL> select * from test; COL1 COL2 ---------- ---------------------------------------- 00001 a000000000b0000 00002 c000000000d0000
パフォーマンス関連のオプション
パフォーマンスに影響するオプションには以下のようなものがあります。DIRECT=true
従来型パス・ロードではなくダイレクト・パス・ロードを使用してロードします。 ダイレクト・パス・ロードは内部的にINSERTの発行やバッファキャッシュの経由等をスキップし、複数CPUが搭載されているサーバではマルチスレッドで処理されるためほとんどの場合処理が高速化されます。 なお、ダイレクトロードの実行中はテーブル全体にロックをかける必要があるため該当テーブルに対して表ロックや行ロックがかかっていない状況で使用する必要があるほか、 insertされるデータは必ず新規ブロックを獲得しそのブロックからinsertされるため連続実行すると領域の利用効率が悪くなりHigh Water Markが引きあがりやすいので注意が必要です。
PARALLEL=true
ダイレクト・パス・ロード時に複数起動したsqlldrから同じ表に対して並列でロードできるようになります。 このパラメータは自動的にパラレル処理になるという動作ではなく、 同じ表へロードするsqlldrを複数起動しても処理できるようになるというパラメータです。 パラレル・ダイレクト・パス・ロードの実行時は必ず新規エクステントが獲得されるため、 パラレル実行しない場合と比較して領域が多く必要になります。 なお、従来型パスロードの場合はもともとパラレルで処理可能なためこのパラメータは不要です。
SKIP_INDEX_MAINTENANCE=true
sqlldr実行後の索引のメンテナンスをスキップするためパフォーマンスが向上します。 このオプションを指定した場合索引のステータスがUNUSABLE(使用不可)となるため処理後にリビルド等を実行して再び有効にする必要があります。 ダイレクト・パス・ロード＋パラレルで実行する場合はSKIP_INDEX_MAINTENANCE=falseに設定されている場合ORA-26002が発生するためこの設定が必要になります。
COLUMNARRAYROWS=＜行数＞
ダイレクト・パス・ロード使用時の列配列数を指定します。 従来型パスロード使用時の場合指定しても無視されます。
STREAMSIZE=＜バイト数＞
ダイレクト・パス・ロード使用時のI/Oストリームサイズを指定します。 従来型パスロード使用時の場合指定しても無視されます。
BINDSIZE=＜バイト数＞
内部的に発行されるinsert文のバインド配列のサイズを設定します。 ダイレクト・パス・ロードの場合はSQL文の発行はスキップされるため設定は無視されます。
ROWS=＜行数＞
一回にCOMMITされる行数を指定します。ダイレクト・パス・ロード＋パラレルで実行する場合は無視されます。
READSIZE=＜バイト数＞
ロードするデータファイルの読み取りバッファを設定します。 データファイルが固定長形式の場合は1行あたりのバイト数の倍数に設定すると無駄なくバッファが利用できます。
UNRECOVERABLE
UNRECOVERABLEはダイレクト・パス・ロード使用時のみ使用可能なオプションで制御ファイル内に指定します。 UNRECOVERABLEを指定した場合REDOへの出力が圧倒的に少なくなるためほとんどの場合数倍処理が高速化します。 その代わり、リカバリに必要なREDOが記録されなくなるためUNRECOVERABLE句付きで処理した後にDISK障害が発生した場合該当テーブルはsqlldrの実行以降のリカバリが不能になり、 更新が失われてしまいます(テーブルの再作成が必要になる)。したがって、更新が失われてよい、または更新されずsqlldrのデータファイルから復旧すればよいような場合以外はUNRECOVERABLE句付きで処理した後には必ずバックアップの取得が必要です。
折りたたみ
展開