【初心者向け】Oracleのローダー機能で指定したテーブルにインサートする方法(少し応用、例文あり)

「Oracleのローダーの基本は分かった!もうちょっとだけステップアップしたい!」と言う方がいると思います。例えば、もう少し高速化したいとか、エラー件数を全部表示したいとかあると思います。今回はローダー実行時のパラメータについて紹介しようと思います。基本的なローダーの使用については以下のリンクを参照願います。

ローダーのコマンドパラメータ

OracleのSQL*Loaderにはパラメータが用意されています。csv等のテキストファイルを単純にインサートするだけであれば特段気にする必要はありませんが、csvファイル自体が何らかの制約がある場合や、実行環境によってちょっとだけ工夫が必要な場合もあります。各所でパラメータの解説をしている先人がいらっしゃるのでコアな話はそちらにお願い致します。基本的なローダーの制御ファイルの記述は以下です。

LOAD DATA
INFILE 'c:\HogeTable.csv'
BADFILE 'c:\HogeTable.bad'
APPEND INTO TABLE TABLE_HOGE
FIELDS TERMINATED BY ","
(
COLUMN1,
COLUMN2,
COLUMN3,
...
)

上記の制御ファイルの内容を少しだけアレンジします。結論から言うと、「OPTIONS(~)」という記述を制御ファイルの先頭に記述します。この「OPTIONS」以降の部分に複数のパラメータを記述することも可能です。パラメータごとにカンマで区切って下さい。

OPTIONS(PRAM_A=X,PRAM_B=Y,...)

上記の様に記述することで複数のパラメータを指定することが可能です。

 

ログファイルのエラー件数の表示を変更したい

ローダーを実行した人でエラーが発生した人は星の数ほどいると思います。しかしながら、エラーが多すぎると途中でロード実行そのものがストップして「何で?」と疑問を抱く人も多いと思います。答えは簡単です。「エラーの許容件数が50件まで」というデフォルトの設定のままだからです。発生エラー件数が50件に到達するとストップしてしまいます。エラー件数の許容を設定するには、以下の様に記述します。

OPTIONS(ERRORS=n)

ERRORS=n』 のnの部分に任意の値を設定すると、設定した値までエラー件数が許容されます。しかしながら、上限をいくら設定しようが日々増えていくデータを扱う業務では現実的ではありません。むしろテスト中であれば全件許容してエラーを全部解消したいはずです。エラーを全件許容する場合は『ERRORS=-1』と記述します。そうすることで、エラーが何件発生しようが、インサートは最後まで実行されます。

  • OPTIONS(ERRORS=n)・・・n件までエラー許容するが、エラーn件で中止
  • OPTIONS(ERRORS=-1)・・・全件エラー許容し、最後まで実行
  • OPTIONS(ERRORS=0)・・・エラーを許容しない

 

ヘッダを読み込みの対象外にしたい

例を挙げれば沢山あると思いますが、客先からエクセルでデータを受領してエクセルのエクスポート機能でcsv出力した時など、そのcsvにヘッダ情報が記載されてると思います。1つや2つのcsvのヘッダを削除するのであれば工数的な問題は大したことでは無いと思いますが、ヘッダ記述のあるcsvが100ファイルだったりする時は日が暮れるどころじゃありません。結論から言うと、ヘッダを除外するパラメータは存在しませんが、ヘッダのみ飛ばしてロードすれば解決します。

OPTIONS(SKIP=n)

SKIP=n』のnの部分に任意の値を設定すれば、先頭から数えてn件目まで実行の対象外としてくれます。今回みたいにヘッダだけを対象外にしたい場合は、OPTIONS(SKIP=1)と記述すれば、最初の1レコード目が読み込みの対象外になります。

 

少しでも早くロードを終わらせたい

高速化とまではいかないかもですが、少しでもロードを早く終わらせたいという気持ちは理解出来ます。作業効率だの何だの言われる世の中なので、仕方ないでしょう。さて、ローダー自体の高速化ですが、その方法は先人たちが色々と研究されているみたいです。コアな部分なので、本記事ではノーコメントとします。ノーコメントとしたいのですが、せっかくなので少しだけ紹介します。尚、ダイレクトパスの話は少し難しいので別の機械ということで、ここでは割愛させて頂きます。

 

コミット件数を指定する

1行ずつコミットすると、当然の様に時間がかかります。ローダーを実行した経験がある人は、コマンドプロンプトの画面にものすごい勢いで「コミット・ポイント」という文字が流れていくのを見届けたことが何回もあると思います。1件1件なんてとてもじゃないですが日が暮れてしまいます。なので、制御ファイルからコミット件数を指定してしまいます。

OPTIONS(ROWS=n)

 『ROWS=n』のnの部分に任意を値を設定すると、コミット件数を設定出来ます。値が小さい場合、その都度コミットが走るので、割と大きな値を設定すればコミットの回数を減らせます。まとめてコミットする件数はOracle側で調整します。100000くらいを設定しておけば大丈夫でしょう。

 

BINDSIZEを指定する

ROWSをいくら大きく指定しても速度が速くなりません。BINDSIZEの上限値でコミットが実行されてしまいます。デフォルトのBINDSIZEは256,000Byteが設定されています。これをパラメータ側で拡張してしまいます。

OPTIONS(BINDSIZE=n)

 『BINDSIZE=n』のnの部分に任意の値を設定します。但し、設定する時はByte単位のため、例えば10MBを指定したい時は10000000と記述します。

 

READSIZEを指定する

csvの件数が多いと、ロード実行に時間を要します。これまたROWSの値が大きくてもローダーの実行時間は長くなるままです。デフォルトでは1,048,576Byte(64KB)なので、こちらもパラメータ指定で値を変更しましょう。

OPTIONS(READSIZE=n)

READSIZE=n』のnの部分に任意の値を設定します。BINDSIZEと同様に10MBを指定したい場合は10000000と記述します。

 

まとめ:パラメータを書いてみる

上記で長々と説明しましたが、複数のパラメータを設定する場合はどのように記述するのかを紹介します。要件としてはこんな感じですね。

  • エラーを全件知りたい
  • csvにヘッダが記載しているので読み込み対象外としたい
  • とにかく早くロードを完了させたい

上記の要件を満たすには、以下のようにパラメータを記述します。

OPTIONS(ERRORS=-1,ROWS=100000,READSIZE=1000000,BINDSIZE=10000000)

このOPTIONSは制御ファイルの先頭に記述します。本記事はあくまでも一例です。パラメータを何も指定せずに実行するよりも速度は向上するはずです。色々な組み合わせがあるので、実際に各端末に合ったものを指定してみてください。