テクノロジー：PL/SQL

BULK COLLECTとFORALLによるバルク処理
Steven Feuerstein著

PL/SQLの理解と利用に関するシリーズ記事のパート9

このシリーズの前回の記事では、PL/SQLのコレクションについて説明しました。コレクションというデータ構造は、プログラム・データのリストを操作するアルゴリズムを実装する場合に非常に便利ですが、さらにPL/SQLにおける一部の強力なパフォーマンス最適化機能の主要な要素でもあります。

この記事では、パフォーマンス最適化機能のうちもっとも重要な、BULK COLLECTとFORALLについて取り上げます。

  • BULK COLLECT：1回のフェッチで複数の行を取得するSELECT文。データ取得が高速化される

  • FORALL：コレクションを使用してデータの複数行をすばやく変更するINSERT、UPDATE、DELETE

ここでのすばやくとはどういう意味か、これらの機能が持つ実際の影響度はどれほどなのかという疑問を持つかもしれません。実際の結果は、実行するOracle Databaseのバージョンとアプリケーション・ロジックの仕様によって異なります。スクリプトをダウンロードして実行することで、1行ごとの挿入のパフォーマンスとFORALLによる挿入のパフォーマンスを比較できます。Oracle Database 11g Release 2を実行する私のラップトップでは、一度に100,000行を挿入する操作に4.94秒かかりました。一方、FORALLを使用した場合、同じ100,000行の挿入に0.12秒しかかかりませんでした。非常に大きな差ですね。

PL/SQL Challengeの正解

前号の"コレクションの操作"で出題されたPL/SQL Challengeの質問では、疎の状態で移入されたコレクションに対する繰り返し処理の実行について、知識をテストしました。正解は選択肢(c)のみです。選択肢(c)は、このタスクを達成するためのもっともシンプルなアルゴリズムです。  


  
DECLARE
  l_names  DBMS_UTILITY.maxname_array;
BEGIN
  l_names (1) := ‘Strawberry’;
  l_names (10) := ‘Blackberry’;
  l_names (2) := ‘Raspberry’;

  DECLARE
    indx  PLS_INTEGER := l_names.FIRST;
  BEGIN

    WHILE (indx IS NOT NULL)
    LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
      indx := l_names.NEXT (indx);
    END LOOP;
  END;
END;
/

PL/SQLはSQL言語と非常に緊密に統合されているのに、PL/SQL内にあるSQL文のパフォーマンス向上のために、なぜ特別な機能が必要になるのかという疑問を持つかもしれません。この疑問に対する説明は、PL/SQLとSQLのそれぞれのランタイム・エンジンが相互に通信する方法、つまりコンテキスト・スイッチと大きく関係します。

コンテキスト・スイッチとパフォーマンス

PL/SQL開発者が記述するほぼすべてのプログラムに、PL/SQL文とSQL文の両方が含まれます。PL/SQL文はPL/SQL文エグゼキュータによって実行され、SQL文はSQL文エグゼキュータによって実行されます。PL/SQLランタイム・エンジンがSQL文を検出すると、いったん停止してSQL文をSQLエンジンに渡します。SQLエンジンはこの渡されたSQL文を実行し、情報をPL/SQLエンジンに返します（図1を参照）。この制御の移行がコンテキスト・スイッチと呼ばれ、コンテキスト・スイッチが起きるたびにオーバーヘッドが発生して、プログラムの全体的なパフォーマンスが低下します。

o52plsql-f1

図1：PL/SQLエンジンとSQLエンジンのスイッチング

具体的な例を見ていきながら、コンテキスト・スイッチをじっくりと理解し、また、FORALLとBULK COLLECTがパフォーマンスにそのような劇的な変化をもたらす理由を確認しましょう。

例として、部門IDと昇給率（%）を受け取り、その部門に所属する全メンバーに指定した率の昇給を与えるプロシージャを記述するようにマネージャーから指示されたとします。そこで、PL/SQLの優れた機能であるカーソルによるFORループと、PL/SQL内でネイティブにSQL文をコールする機能を利用して、リスト1のようなコードができました。

コード・リスト1：FORループを使用したincrease_salaryプロシージャ 


  
PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      UPDATE employees emp
         SET emp.salary = emp.salary + 
             emp.salary * increase_salary.increase_pct_in
       WHERE emp.employee_id = employee_rec.employee_id;
   END LOOP;
END increase_salary;

部門15に100人の従業員が所属していると仮定します。次のブロックを実行すると、 


  
BEGIN
   increase_salary (15, .10);
END;

PL/SQLエンジンはSQLエンジンに、更新される行ごとに1回、合計で100回も"スイッチング"します。AskTom（asktom.oracle.com）のTom Kyteはこのように1行ずつスイッチングすることを"スローバイスロー処理"と呼んでいます。当然ながら、このような処理は避けるべきです。

これ以降は、PL/SQLのバルク処理機能を使用して"スローバイスロー処理"から逃れる方法について説明します。しかし、まずはSQL内部でできる限り多くの対応を行ってPL/SQLとSQLのコンテキストのスイッチングを避けることができるかを、かならず確認するようにしてください。

increase_salaryプロシージャをもう一度見てみましょう。このSELECT文は部門内のすべての従業員を識別しています。また、このUPDATE文は各従業員に対して実行され、全員に同じパーセンテージを適用しています。そのようなシンプルなシナリオでは、カーソルによるFORループを使用する必要はまったくありません。このプロシージャは、リスト2のコードにまで単純化できます。

コード・リスト2：FORループを使用しない、単純化されたincrease_salaryプロシージャ 


  
PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   UPDATE employees emp
      SET emp.salary =
               emp.salary
             + emp.salary * increase_salary.increase_pct_in
    WHERE emp.department_id = 
             increase_salary.department_id_in;
END increase_salary;

このコードでは、わずか1回のコンテキスト・スイッチにより、1つのUPDATE文が実行されます。すべての作業がSQLエンジン内で実行されます。

しかし、現実に近いシナリオでは、人生（そしてコード）はそれほど単純ではありません。データ操作言語（DML）文の実行前に、他の手順が必要になることの方が多いのです。たとえば、increase_salaryプロシージャの例で、従業員に昇給の資格があるかどうかをチェックし、資格がない場合は電子メール通知を送信する必要があるということにしましょう。その場合のプロシージャは、リスト3のバージョンのようになります。

コード・リスト3：資格チェックを追加したincrease_salaryプロシージャ 


  
PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
   l_eligible   BOOLEAN;
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      check_eligibility (employee_rec.employee_id,
                         increase_pct_in,
                         l_eligible);

      IF l_eligible
      THEN
         UPDATE employees emp
            SET emp.salary =
                     emp.salary
                   +   emp.salary
                     * increase_salary.increase_pct_in
          WHERE emp.employee_id = employee_rec.employee_id;
      END IF;
   END LOOP;
END increase_salary;

もうSQLだけでは、すべてに対応できません。あきらめて"スローバイスロー処理"の運命を辿るしかないのでしょうか。PL/SQLにBULK COLLECTとFORALLがなければ、そうなるでしょう。

PL/SQLでのバルク処理

PL/SQLのバルク処理機能は、PL/SQLエンジンからSQLエンジンへの通信に必要となるコンテキスト・スイッチの回数を削減する目的で設計されています。

BULK COLLECT句は、1回のコンテキスト・スイッチにより複数の行をフェッチして1つ以上のコレクションに格納する場合に使用します。

FORALL文は、同じDML文を異なるバインド変数値に対して繰り返し実行する必要がある場合に使用します。increase_salaryプロシージャのUPDATE文はこのFORALL文のシナリオに一致します。このシナリオでは、文を新しく実行するたびに従業員IDだけが変わります。

これらの機能がコンテキスト・スイッチにどのように影響するか、およびこれらの機能を利用するためにどのようにコードを変更する必要があるかについて、リスト4のコードを使用して説明します。

コード・リスト4：increase_salaryプロシージャのバルク処理 


  
1  CREATE OR REPLACE PROCEDURE increase_salary (
 2     department_id_in   IN employees.department_id%TYPE,
 3     increase_pct_in    IN NUMBER)
 4  IS
 5     TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
 6             INDEX BY PLS_INTEGER; 
 7     l_employee_ids   employee_ids_t;
 8     l_eligible_ids   employee_ids_t;
 9
10     l_eligible       BOOLEAN;
11  BEGIN
12     SELECT employee_id
13       BULK COLLECT INTO l_employee_ids
14       FROM employees
15      WHERE department_id = increase_salary.department_id_in;
16
17     FOR indx IN 1 .. l_employee_ids.COUNT
18     LOOP
19        check_eligibility (l_employee_ids (indx),
20                           increase_pct_in,
21                           l_eligible);
22
23        IF l_eligible
24        THEN
25           l_eligible_ids (l_eligible_ids.COUNT + 1) :=
26              l_employee_ids (indx);
27        END IF;
28     END LOOP;
29
30     FORALL indx IN 1 .. l_eligible_ids.COUNT
31        UPDATE employees emp
32           SET emp.salary =
33                    emp.salary
34                  + emp.salary * increase_salary.increase_pct_in
35         WHERE emp.employee_id = l_eligible_ids (indx);
36  END increase_salary;
行 説明
5-8 新しいネストした表型と、この型に基づく2つのコレクション変数を宣言します。一方のl_employee_ids変数は、部門内の全従業員のIDを保持します。もう一方のl_eligible_idsは、昇給の資格がある全従業員のIDを保持します。
12-15 BULK COLLECTを使用して、指定した部門の全従業員のIDをフェッチし、l_employee_idsコレクションに格納します。
17-28 昇給の資格をチェックします。資格がない場合は、電子メールが送信されます（注：check_eligibilityの実装はこの記事では割愛します）。資格がある場合は、IDをl_eligible_idsコレクションに追加します。
30-35 FORALL文を使用して、l_eligible_idsコレクション内の従業員IDにより識別されるすべての行を更新します。

リスト4には、この改良後の新しいincrease_salaryプロシージャのコードに関する説明も含まれています。次の3つの実行フェーズがあります。 

  • BULK COLLECTにより行をフェッチし、1つ以上のコレクションに格納します。このステップでは、1回のコンテキスト・スイッチが必要です。

  • 必要に応じてコレクションの内容を変更します（この例の場合、資格のない従業員を削除します）。

  • 変更後のコレクションを使用して、FORALLにより表を更新します。 

FORALLは、PL/SQLエンジンとSQLエンジンを切り替えて1行ずつ更新するのではなく、すべての更新を"まとめて"、1回のコンテキスト・スイッチによりそれらの更新をSQLエンジンに渡します。その結果、パフォーマンスが劇的に向上します。 

これ以降、BULK COLLECTについて掘り下げ、その後FORALLについて説明します。

BULK COLLECTについて

バルク処理を利用して問合せを行うには、BULK COLLECTをINTOキーワードの前に置いて、1つ以上のコレクションをINTOキーワードの後に指定します。BULK COLLECTの動作方法について知っておくべきことは次のとおりです。 

  • 3つのコレクション型（連想配列、ネストした表、VARRAY）のすべてで使用できる。

  • フェッチした情報を個々のコレクション（SELECTリスト内の式ごとに1つ）に格納することも、レコードの1つのコレクションに格納することもできる。

  • コレクションは常に、インデックス値1から開始し、密に移入される。

  • 1行もフェッチされなかった場合、コレクションからすべての要素が削除される。

リスト5に、2つの列の値をフェッチしてレコードのコレクションに格納する例を示します。

コード・リスト5：2つの列の値をフェッチしてコレクションに格納 


  
DECLARE
   TYPE two_cols_rt IS RECORD
   (
      employee_id   employees.employee_id%TYPE,
      salary        employees.salary%TYPE
   );

   TYPE employee_info_t IS TABLE OF two_cols_rt;

   l_employees   employee_info_t;
BEGIN
   SELECT employee_id, salary
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = 10;
END;

大量の行をフェッチしていると、入力中のコレクションがセッション・メモリを大量に消費することで、エラーが発生する場合があります。そのようなエラーを防ぐために、BULK COLLECTのLIMIT句を使用できます。たとえば、ある単一の部門に数万人の従業員がいて、20,000人の従業員IDをコレクション内に格納するのに十分なメモリがセッションにないという場合が考えられます。

そのような場合は、リスト6のアプローチを使用します。

コード・リスト6：指定した行数までのフェッチ 


  
DECLARE
   c_limit PLS_INTEGER := 100;

   CURSOR employees_cur
   IS
      SELECT employee_id
        FROM employees
       WHERE department_id = department_id_in;

   TYPE employee_ids_t IS TABLE OF 
      employees.employee_id%TYPE;

   l_employee_ids   employee_ids_t;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH employees_cur
      BULK COLLECT INTO l_employee_ids
      LIMIT c_limit;

      EXIT WHEN l_employee_ids.COUNT = 0;
   END LOOP;
END;

このアプローチでは、まずフェッチするすべての行を識別するカーソルをオープンします。次に、ループ内部で、FETCH-BULK COLLECT-INTOを使用して、c_limit定数（100に設定）に指定した行数までフェッチします。これで、フェッチする必要のある行数にかかわらず、セッションで消費されるメモリ容量がこの100行に必要となる容量以内に抑えられます。しかも、バルク問合せのパフォーマンスが向上するというメリットも得られます。

FORALLについて

ループ内でDML文を実行するときは常に、FORALLを使用するようにそのコードを変換してください。その変換によるパフォーマンス向上に開発者は驚き、ユーザーも満足するでしょう。

FORALL文はループではなく、PL/SQLエンジンに対して次のことを伝える宣言文です。"通常なら1行ずつ実行されるDML文をすべて生成し、それらのDML文を1回のコンテキスト・スイッチでSQLエンジンに送信する"

リスト4の30～35行目から分かるように、FORALL文の"ヘッダー"の見た目は数値によるFORループと同じですが、LOOPキーワードやEND LOOPキーワードはありません。

FORALLについて知っておくべきことは次のとおりです。

  • 各FORALL文には1つのDML文のみを記述できる。ループに2回の更新と1回の削除が含まれる場合は、3つのFORALL文を記述する必要がある。

  • PL/SQLでは、FORループの場合と同様に、FORALLイテレータ（リスト4の30行目のindx）を整数として宣言する。このイテレータと同じ名前の変数を宣言する必要はなく、また宣言すべきではない。

  • DML文の少なくとも1か所で、コレクションを参照して、そのコレクションのインデックス値としてFORALLイテレータを使用する必要がある（リスト4の35行目を参照）。

  • FORALLヘッダーでIN low_value . . . high_value構文を使用する場合、FORALL文の内部で参照するコレクションは密に入力されている必要がある。つまり、low_valueからhigh_valueまでのすべてのインデックス値が定義済みである必要がある。

  • コレクションが密に入力されていない場合は、FORALLヘッダーでINDICES OF構文またはVALUES OF構文を使用する必要がある。 

FORALLとDMLエラー

表に10,000行を挿入するプログラムを記述し、9,000行を挿入後、9,001行目の挿入がDUP_VAL_ON_INDEXエラー（一意索引違反）により失敗したとします。この場合、SQLエンジンはそのエラーをPL/SQLエンジンに返します。ここでFORALL文がリスト4のように記述されていると、PL/SQLでFORALL文が終了します。残りの999行は挿入されません。

途中でエラーが発生した場合でもPL/SQLエンジンにできるだけ多くのDML文を実行させるには、FORALLヘッダーにSAVE EXCEPTIONS句を追加します。そうしておけば、SQLエンジンでエラーが発生した場合に、PL/SQLエンジンがその情報をSQL%BULK_EXCEPTIONSという擬似コレクションに保存して、文の実行を継続します。すべての文の実行後に、PL/SQLでORA-24381エラーが発生します。

このエラーを例外セクションで捕捉して、SQL%BULK_EXCEPTIONSの内容に対して繰り返し処理を実行し、発生したエラーを把握できます（また、そうすべきです）。その後、エラー情報をログ表に書き込む、DML文のリカバリを試みるなどの対応をとることができます。

リスト7に、FORALL文でのSAVE EXCEPTIONSの使用例を示します。この例では、エラーが発生したl_eligible_idsコレクションのインデックスと、SQLエンジンで発生したエラー・コードを画面に表示します。

コード・リスト7：FORALLでのSAVE EXCEPTIONSの使用 


  
BEGIN
   FORALL indx IN 1 .. l_eligible_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees emp
         SET emp.salary =
                emp.salary + emp.salary * increase_pct_in
       WHERE emp.employee_id = l_eligible_ids (indx);
EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE = -24381
      THEN
         FOR indx IN 1 ..SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
               || ‘:‘

               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         END LOOP;
      ELSE
         RAISE;
      END IF;
END increase_salary;

SQLからPL/SQLへ

この記事ではおもに、PL/SQLブロック内部からSQL文を実行したときに起きる、PL/SQLエンジンからSQLエンジンへのコンテキスト・スイッチについて説明しています。しかし、コンテキスト・スイッチは、ユーザー定義のPL/SQLファンクションをSQL文の内部から呼び出したときにも起きるということも覚えておいてください。

たとえば、開始ポイントから終了ポイントまでの文字列を返すbetwnstrというファンクションを記述したとします。このファンクションのヘッダーは次のとおりです。 


  
FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2

このファンクションは、次のようにコールできます。 


  
SELECT betwnstr (last_name, 2, 6) 
  FROM employees
 WHERE department_id = 10

このemployees表の行数が100あり、そのうちの20行のdepartment_idが10に設定されている場合、このファンクションを実行するために、SQLからPL/SQLへのコンテキスト・スイッチが20回起きることになります。

そのため、SQLでのユーザー定義ファンクションのすべての呼出しについて細心の注意を払ってください。特に、文のWHERE句内のファンクションについては注意する必要があります。次の問合せを考えてみましょう。 


  
SELECT employee_id
  FROM employees
 WHERE betwnstr (last_name, 2, 6) = 'MITHY'

この問合せでは、betwnstrファンクションが100回実行され、100回のコンテキスト・スイッチが起きます。

次のステップ 

ダウンロード

Oracle Database 11g

この記事のスクリプト

 テスト  PL/SQLの知識

 その他の記事 PL/SQLの基礎、パート1～8  

詳細情報 INDICES OFとVALUES OF

疎コレクションを使用するFORALL

IN low_value .. high_value構文をFORALLとともに使用する場合に、その範囲内に未定義のインデックス値があると、Oracle Databaseの"ORA-22160: element at index [N] does not exist"エラーが発生します。

このエラーを防ぐために、INDICES OF句またはVALUES OF句を使用できます。これらの句の使用法を確認するために、リスト4のコードを見直してみましょう。このバージョンのincrease_salaryでは、昇給の資格がある従業員のIDを保持するl_eligible_idsという2つ目のコレクションを宣言しています。

この方法の代わりに、次のようにl_employee_idsコレクションから資格のないIDをすべて削除することもできます。 


  
FOR indx IN 1 .. l_employee_ids.COUNT
   LOOP
      check_eligibility (l_employee_ids (indx),
                         increase_pct_in,
                         l_eligible);

      IF NOT l_eligible
      THEN
         l_employee_ids.delete (indx);
      END IF;
   END LOOP;

しかし、今度はl_employee_idsコレクションにギャップが生じる可能性があります。つまり、BULK COLLECTによって移入された1から最大のインデックス値までに、未定義のインデックス値が含まれる可能性があります。

この場合でも心配はいりません。FORALL文を次のように変更するだけです。 


  
FORALL indx IN INDICES OF l_employee_ids
   UPDATE employees emp
      SET emp.salary =
               emp.salary
             + emp.salary * 
                increase_salary.increase_pct_in
    WHERE emp.employee_id = 
      l_employee_ids (indx);

このコードでは、固定の値範囲を指定するのではなく、l_employee_idsに定義されたインデックス値のみを使用するようにPL/SQLエンジンに指示しています。Oracle Databaseでは未定義のインデックス値が単にスキップされるため、ORA-22160エラーは発生しません。

これはもっとも単純なINDICES OFの使用法です。INDICES OFの複雑な使用法や、VALUES OFを使用すべき状況とその使用法については、このドキュメントを参照してください。

バルク処理でコードを強化

コードのパフォーマンスを最適化することは、困難で時間のかかる作業です。しかし、まだBULK COLLECTやFORALLを利用するようにコードを修正していなければ、パフォーマンスの最適化は比較的簡単で爽快にもなります。もぎ取りやすい果実がまだ残っているのですから。    

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは以下の他、 PL/SQL Challenge にも掲載されます。PL/SQL Challengeは、PL/SQL言語やSQL、Oracle Application Expressに関するオンライン・クイズを提供するWebサイトです。   

次のように従業員表を作成して移入しました。


  
CREATE TABLE plch_employees 
				(    
				employee_id   INTEGER,  
				   last_name     VARCHAR2 (100) 
				) 
				/   
				BEGIN    
				INSERT INTO plch_employees   
				      VALUES (100, ‘Picasso’); 
				   INSERT INTO plch_employees   
				      VALUES (200, ‘Mondrian’); 
				   INSERT INTO plch_employees    
				      VALUES (300, ‘O’’Keefe’);  
				   COMMIT; 
				END;
				/

質問

次のブロックのうち、表のすべての従業員について、姓を大文字に変更するものはどれでしょうか。

a.


  
DECLARE
   TYPE ids_t IS TABLE OF plch_employees.employee_id%TYPE;

   l_ids   ids_t := ids_t (100, 200, 300);
BEGIN

   FORALL indx IN 1 .. l_ids.COUNT
   LOOP
      UPDATE plch_employees
         SET last_name = UPPER (last_name)
       WHERE employee_id = l_ids (indx);

   END LOOP;       
END;
/

b.


  
DECLARE
   TYPE ids_t IS TABLE OF plch_employees.employee_id%TYPE;


   l_ids   ids_t := ids_t (100, 200, 300);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE plch_employees
         SET last_name = UPPER (last_name)
       WHERE employee_id = l_ids (indx);
END;
/

c.


  
BEGIN
   UPDATE plch_employees
       SET last_name = UPPER (last_name);
END;
/

d.


  
DECLARE
   TYPE ids_t IS TABLE OF plch_employees.employee_id%TYPE;

   l_ids   ids_t := ids_t (100, 200, 300);
BEGIN
   FORALL indx IN INDICES OF l_ids
      UPDATE plch_employees
         SET last_name = UPPER (last_name)

       WHERE employee_id = l_ids (indx);
END;
/

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください。

ご意見ご感想をお寄せください。