e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第59回 「バルク・バインド(DMLの場合)」

2013.05.27

こんにちは。インストラクターの蓑島です。

今回から新しいテーマとして「バルク・バインド」を解説します。
PL/SQLブロックで、配列の値を使ってSQL文をループ処理で繰り返し実行しているときに、今回紹介するバルク・バインドの構文に書き換えると、パフォーマンスが大幅にアップできます。

まずバルク・バインドとは何か?を理解するには、PL/SQLブロックが2つのエンジンを使って実行されることを理解する必要があります。
一つはPL/SQLエンジン、もう一つはSQLエンジンです。

無名ブロックであろうが、ストアドプロシージャであろうが、とにかくPL/SQLブロックはまず、PL/SQLエンジンに送り込まれてそこでブロック内の処理を順番どおりに実行します。
しかし、そのブロック内の処理がSQL文(INSERT, UPDATE, DELETEなど)であるときは、そのSQL文および処理する値を別のエンジンであるSQLエンジンに送りそこでSQL文を処理し、終わればまたPL/SQLエンジンに戻りブロック内の残りの処理を実行します。
つまり、PL/SQLブロック内でSQL文を実行するときは別のエンジンに切り替わるわけです。

ということは、ループ処理でSQL文を繰り返し実行しているときは、ループの回数だけ何度もエンジンが切り替わることになります。
この切り替えの回数が多い場合は、そのこと自体が大きなオーバーヘッドとなり結果としてPL/SQLブロックの実行時間が長くなります。

通常このようなループ処理では、配列の値を使ってSQL文をループ処理しますが、その配列を丸ごとSQLエンジンに送り込み、そこでそのSQL文を一括実行することができます。
そうすれば、ループの繰り返し回数がどんなに多くても、エンジンの切り替えが1回で済むのです。
それによりPL/SQLブロックの実行時間を大幅に短縮できます。これがバルク・バインドと呼ばれている機能です。

バルク・バインドにはもう1種類別の系統の処理があります。
上で解説した内容はDML文(INSERT, UPDATE, DELETE)におけるバルク・バインドなのですが、もう1つの系統は、SELECT INTOや明示カーソルのFETCH INTOの場合のバルク・バインドです。
SELECT INTO や、FETCH INTO は結果としての1行を変数に戻します。
よって複数行を戻したければループ処理する必要があります。
しかし、バルク・バインドを使えばその複数行を1回の操作で配列の変数に戻せます。
これもやはり2つのエンジンの切り替えを1回で済ませることになります。

SELECTやFETCHのバルク・バインドについては別の機会に詳しく解説しますので、まずはDMLにおけるバルク・バインドを今回から数回にわたり解説します。
早速、具体例を実行してみましょう。

まず、いきなりバルク・バインドではなく、配列の値を使って普通にループ処理で表にINSERTを行います。
その後でバルクバインドの構文でINSERTを行います。
ループ処理でのINSERTと、バルク・バインドによるINSERTの記述の違いはほんのわずかです。

ではまず表を作ります。
SQL> CREATE TABLE TEST01 ( A  NUMBER, B  VARCHAR2(10));

表が作成されました。

ご覧のように A列、B列だけの単純な表です。

では、最初は普通のループ処理でこの表にINSERTします。

SQL> DECLARE
  2    /***********************************/
  3    -- A列用の配列
  4   /***********************************/
  5    TYPE A_TAB_TYPE IS TABLE OF NUMBER        --型の宣言
  6    INDEX BY BINARY_INTEGER;
  7    A_TAB  A_TAB_TYPE;                        --その型の変数宣言
  8    /***********************************/
  9    -- B列用の配列
 10   /***********************************/
 11    TYPE B_TAB_TYPE IS TABLE OF VARCHAR2(10)  --型の宣言
 12    INDEX BY BINARY_INTEGER;
 13    B_TAB B_TAB_TYPE;                         --その型の変数宣言
 14  BEGIN
 15    /***********************/
 16    -- 実行部
 17    /***********************/
 18    -- 配列に値を格納する
 19    /***********************/
 20    A_TAB(1) := 10;  B_TAB(1) := 'AB';
 21    A_TAB(2) := 20;  B_TAB(2) := 'CD';
 22    /*******************************************/
 23    -- その配列を使ってループ処理でINSERT
 24    /*******************************************/
 25    FOR I IN  A_TAB.FIRST..A_TAB.LAST  LOOP
 26       INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
 27    END LOOP;
 28  END;
 29  /

PL/SQLプロシージャが正常に完了しました。

PL/SQLプロシージャが正常に完了しました。

簡単に解説すると、上のブロックでは、5-7行目、11-13行目でそれぞれA列用、B列用の配列(A_TAB, B_TAB)を宣言してます。
そして実行部の20-21行目でそれぞれの配列に値を2ずつ格納してます。
そしてその配列を使って25-27行目でループ処理により、TEST01表にINSERTしています。
なお、27行目の 「A_TAB.FIRST」は配列A_TABの一番小さな添え字、すなわち「1」です。
同様に「A_TAB.LAST」とは配列A_TABの一番大きな添え字、すなわち「2」です。
よって27行目は以下のように書くこともできます。
「FOR I IN 1..4  LOOP」これは、添え字変数I を1から4までの値でループするという意味です。

INSERTした行を確認します。
SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        10 AB
        20 CD

たしかに2行INSERTされていますね。

では、一旦行を削除して、今度はバルク・バインドで同じことを行います。

SQL> DELETE FROM TEST01;

2行が削除されました。

以下はバルク・バインドの場合です。
上記の27-29行目と下記の記述の27-28行目に注目してください。他はすべて上記と同じ記述です。

SQL> DECLARE
  2    /***********************************/
  3    -- A列用の配列
  4   /***********************************/
  5    TYPE A_TAB_TYPE IS TABLE OF NUMBER        --型の宣言
  6    INDEX BY BINARY_INTEGER;
  7    A_TAB  A_TAB_TYPE;                        --その型の変数宣言
  8    /***********************************/
  9    -- B列用の配列
 10   /***********************************/
 11    TYPE B_TAB_TYPE IS TABLE OF VARCHAR2(10)  --型の宣言
 12    INDEX BY BINARY_INTEGER;
 13    B_TAB B_TAB_TYPE;                         --その型の変数宣言
 14  BEGIN
 15    /***********************/
 16    -- 実行部
 17    /***********************/
 18    -- 配列に値を格納する
 19    /***********************/
 20    A_TAB(1) := 10;  B_TAB(1) := 'AB';
 21    A_TAB(2) := 20;  B_TAB(2) := 'CD';
 22    /*******************************************/
 23    -- その配列を使ってバルク・バインドでINSERT
 24    /*******************************************/
 25    FORALL I IN  A_TAB.FIRST..A_TAB.LAST
 26       INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
 27  END;
 28  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        10 AB
        20 CD

以下に上の二つの処理、すなわちループ処理とバルク・バインド処理を抜粋したのでよく見比べてください。

 22    /*******************************************/
 23    -- その配列を使ってループ処理でINSERT
 24    /*******************************************/
 25    FOR I IN  A_TAB.FIRST..A_TAB.LAST  LOOP
 26       INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
 27    END LOOP;

 22    /*******************************************/
 23    -- その配列を使ってバルク・バインドでINSERT
 24    /*******************************************/
 25    FORALL I IN  A_TAB.FIRST..A_TAB.LAST
 26       INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));

違いは以下の点です。

1. バルク・バインドでは「FOR」ではなく「FORALL」である。
2. バルク・バインドでは、「LOOP」と「END LOOP;」がない(つまりループ処理ではない)

いかがですか?ほんとにわずかな構文の違いですね。

なお、LOOP ~ END LOOP;間のSQL文が複数の場合はバルク・バインドの構文にできませんので注意してください。

まとめるとDML文(INSERT,UPDATE, DELETE)のバルクバインドの基本構文は以下のとおりです。

   FORALL  添え字変数  IN  下限値..上限値
                配列を使った1つのDML文;

  (※添え字変数は宣言不要です)

簡単ですね。
もちろんいろいろなバリエーションがあり、違う構文も可能です。
また違う構文でないと処理できないケースもあります。おいおい解説していきたいと思います。

それでは今回はここまでにいたします。
次回はバルク・バインドがいかに効果が高いか実際にコーディングしてベンチマークテストを行う予定です。
パッケージで配列変数を定義してそこに大量のデータ(数十万から数百万件)を格納しておいて、それをバルクバインドで表にINSERTする場合と普通のループ処理でINSERTする場合とでどれほど時間が違うのかを比較してみます。かなりの違いとなります。

それでは次回、ご期待ください。

先頭へ戻る