PLSQL バルク処理フェッチ＆インサート時間測定も

こんにちは！健史です。

PLSQLで「バルクフェッチ、バルクインサート」のサンプルプログラムを作成しました。

オラクルのサイトを参考にしています。

バルクインサート - オラクル・Oracle PL/SQL 入門

バルクインサート処理 (FORALL) は 1 回のコンテキストスイッチで大量のレコードを処理することができる。このコンテキストスイッチの回数を減らすことによって大量データの処理時のレスポンスを向上することが可能になる。

自分の手を動かして作成することで、理解が深まりました。

目　次　

プログラム
補足
処理時間の比較
その他

プログラム

create or replace PROCEDURE PROC_BULK1 IS
--1.バルク サイズ定義
  BULK_SIZE CONSTANT PLS_INTEGER := 2000;  -- コンスタントでなくても良い
--2.カーソル定義
  CURSOR CUR1 IS
    SELECT * FROM 売上履歴
    ORDER BY 連番 ;
--3.バルク　テーブル等定義
--  バルク FETCH用テーブル定義
  TYPE IN1TBL IS TABLE OF 売上履歴%ROWTYPE INDEX BY BINARY_INTEGER;
  IN1REC IN1TBL;
--  バルク INSERT用テーブル定義   
  TYPE OT1TBL IS TABLE OF 売上履歴集計１%ROWTYPE INDEX BY BINARY_INTEGER;
  OT1REC OT1TBL;
--  エラーハンドラ用 ORA-24381: DML配列にエラーがあります。
  vBulkErrors  PLS_INTEGER := 0;
  eBulkProcessNotComplete EXCEPTION;
  PRAGMA EXCEPTION_INIT(eBulkProcessNotComplete, -24381);
--4.ワークを定義
  入力件数 NUMBER(10);
  出力件数 NUMBER(10);
  WK_TIMESTAMP TIMESTAMP;
--5.ＦＵＮＣＴＩＯＮ定義 必要に応じて定義

--6.メイン処理
BEGIN
--6-1.初期処理
  OPEN CUR1;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE 売上履歴集計１';
  入力件数 := 0;
  出力件数 := 0;
  SELECT TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff5') INTO WK_TIMESTAMP FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('開始時刻：' || WK_TIMESTAMP);
--6-2.順次処理
  LOOP
--  バルク FETCH,INSERT用テーブル初期クリア 
    IN1REC.DELETE;
    OT1REC.DELETE;
--  バルク処理
--  １).FETCH
    FETCH CUR1 BULK COLLECT INTO IN1REC LIMIT BULK_SIZE;
    EXIT WHEN IN1REC.COUNT = 0;
--  2).編集処理
    FOR i in 1 .. IN1REC.COUNT
    LOOP
      入力件数                 := 入力件数 + 1; 
      OT1REC(i).キー１         := IN1REC(i).キー１;
      OT1REC(i).キー２         := IN1REC(i).キー２;
      OT1REC(i).キー３         := IN1REC(i).キー３;
      OT1REC(i).連番           := IN1REC(i).連番;
      OT1REC(i).数量           := IN1REC(i).数量;
      OT1REC(i).単価           := IN1REC(i).単価;
      OT1REC(i).団体コード     := IN1REC(i).団体コード;
      OT1REC(i).件数           := 0;
      出力件数                 := 出力件数 + 1; 
    END LOOP;
--  3).INSERT
    BEGIN
      FORALL i in 1..OT1REC.COUNT SAVE EXCEPTIONS  -- ← エラーが発生しても継続
        INSERT INTO 売上履歴集計１ VALUES OT1REC(i);
      COMMIT;
      EXCEPTION
      WHEN eBulkProcessNotComplete THEN
        vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
        -- PROC_BULK_ERROR_HANDLER;
    END;    
  END LOOP;
--7.終了処理
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('入力件数：' || 入力件数);
  DBMS_OUTPUT.PUT_LINE('出力件数：' || 出力件数);
  DBMS_OUTPUT.PUT_LINE('エラー数:'  || vBulkErrors);
  SELECT TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff5') INTO WK_TIMESTAMP FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('終了時刻：' || WK_TIMESTAMP);
  CLOSE CUR1;
END;

補足

・「バルク FETCH,INSERT用テーブル初期クリア」の理由

--  バルク FETCH,INSERT用テーブル初期クリア 
    IN1REC.DELETE;
    OT1REC.DELETE;

上記をコメントにして、110件の売上履歴を作成し、バルクサイズを[50]で実行しました。

[DBMS_OUTPUT.PUT_LINE('出力件数：' || 出力件数);]の件数は、110件です。

ですが、テーブル[売上履歴集計１]には、150件のデータが作成されていました。

確認したところ、51-100件処理したときの61-100件のデータが重複して作成されていました。

OT1RECをクリアしないためにデータが残っており、[OT1REC.COUNT]もクリアされず"50"のままになっていることが原因と考えています。

[IN1REC.DELETE;]をコメントしても正常に処理されますが、これはFETCHしたときに[IN1REC.COUNT]が設定されるのでしょう。

わかりやすくするため、このプログラム、及び、以降で記事にするプログラムでは、入出力テーブルをクリアすることにしました。

処理時間の比較

1,500,000件のデータを単純に、FETCH→移送→INSERTするだけです。

バルクサイズを2000件と1件で比較してみました。
1件であれば、バルクを使用せずに処理したことと同じです。
(厳密には、違いがあるかもしれませんが)

・2,000件　　0:11
・1件　　　　2:04

開始時刻：25-04-01 12:55:46.479000
入力件数：1500000
出力件数：1500000
エラー数:0
終了時刻：25-04-01 12:55:57.854000

開始時刻：25-04-01 12:56:43.679000
入力件数：1500000
出力件数：1500000
エラー数:0
終了時刻：25-04-01 12:58:47.478000

その他

実際に使用したSQLDEVELOPERの画面を添付します。