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が搭載されているサーバではマルチスレッドで処理されるためほとんどの場合処理が高速化されます。
従来型とダイレクトロードは比較して以下の動作の差異が発生することに注意する必要があります。
・テーブル全体に対して排他ロックを獲得するため他セッションからの更新は一切できない
・ロード中は全索引を無効化し、ロード完了後に索引を再構築(SKIP_INDEX_MAINTENANCE=trueの場合)するためロード中にselectが発行された場合FULLスキャンになる
・ロード中全索引を無効化するため一意制約に違反するデータもロードすることが可能になる(一意制約違反のデータがあった場合索引が再構築できずUNUSABLE状態のままになる)
・ロード完了後に索引を再構築するという動作上APPEND指定で大量の既存データへ少量のデータを追加ロードする場合は従来型よりもパフォーマンスは悪くなる
・ロードデータは必ず新規ブロックを獲得しそのブロックに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はダイレクト・パス・ロード時のみ有効なオプションでREDOログの出力が最小限になるオプションです。(制御ファイル内で指定します)
REDOログへの書き込みがボトルネックとなっている場合処理時間がかなり高速化しますが、
REDOが出力されなくなるということは該当表のリカバリができなくなることを意味します。つまり、UNRECOVERABLE処理後にDISK障害等が発生した場合リカバリしてもsqlldrの実行直前までしかデータが復元できず
それ以降の更新は失われます(該当テーブル以外の復元は可能、該当テーブルも再作成等は可能)。
したがって、更新が失われてよい、またはその他復元方法が用意されているといった場合以外はUNRECOVERABLEで処理した後には必ず物理バックアップの取得が必要です。
折りたたみ
展開