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

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

第61回 「バルク・バインド(SELECT BULK COLLECT INTOの場合)」

2013.06.10

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

前回は、バルク・バインドのスピードがたいへん速いことを検証しました。
50万行のINSERTをループ処理で行うと15秒かかりましたが、バルクバインドならわずか0.6秒という驚きの結果でした。
配列でDML文をループ処理している場合は、ぜひ試してみてください。

さて、バックナンバー第59回で触れたように、バルク・バインドにはDML文の他、SELECT INTOとFETCH INTOのバルク・バインドもあります。
今回はSELECT INTO のバルク・バインドを解説します。
(次回はFETCH INTO のバルク・バインドを解説します)

SELECT INTO 文というと、1行だけ結果が返るときしか使えないものですね。
(バックナンバー第6回と、第7回参照)

ですから複数行を配列に格納したいときに、SELECT INTO文を使うのであれば、1行づつのSELECT INTO 文をループ処理しなければいけません。

しかし、バルク・バインドを使うと、ループ処理を使わずに、SELECT INTO文で複数行を一括で配列変数に格納できます。

バルク・バインドのSELECT INTO文は、正確にはSELECT BULK COLLECT INTOの構文となります。
以下の通りです。

/*********************************/
--  SELECT  BULK COLLECT INTO 文の構文         
/*********************************/
SELECT  列1, 列2,・・・ BULK COLLECT  INTO  配列1, 配列2, ・・・ FROM  表名・・・

または、単純に、以下の構文

SELECT *  BULK COLLECT INTO レコード型配列  FROM 表名・・・

(※格納された配列の添え字は1から始まります。1,2,3,・・・)

簡単な構文ですね。

普通のSELECT INTO文との違いは、INTO の前にBULK COLLECT というキーワードがあり、 INTO の先の変数が複数行を格納できる配列変数であるという点です。
これによりループ処理をしなくても、対象となるすべての行が一括で配列に格納されます。

では早速試してみましょう。

前回使ったTEST01表に50万行を格納しておいて、そこからループ処理でSELECT INTO 文を使い、1行づつ、配列に格納する場合と、SELECT BULK COLLECT INTO文を使って一括で配列する場合を比較します。
構文を確認してください。また、時間も計測してみましょう。

前回作ったTEST01表です。

SQL> DESC TEST01
 名前                                      NULL?    型
 --------------------------------------------------------------------------
 A                                                  NUMBER
 B                                                  VARCHAR2(10)

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

表を空にします。

SQL> TRUNCATE TABLE TEST01;

表が切り捨てられました。

SQL>

早速、50万行INSERTしておきます。

SQL> BEGIN
  2    FOR I IN  1..500000  LOOP
  3       INSERT INTO TEST01(A,B) VALUES (I,'BBBBBB');
  4    END LOOP;
  5  END;
  6  /

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

SQL> COMMIT;

コミットが完了しました。

これでTEST01表に50万行が格納されました。A列の値は1~500000まで、B列の値は全て、'BBBBBB'です。

次に実際に検証をする前にA列を主キーに設定しましょう。
それによりA列の条件指定でSELECT INTOで1行を取得する際に主キー索引が使われますので非常に高速になります。

SQL> ALTER TABLE TEST01
  2  ADD CONSTRAINT PK_TEST01 PRIMARY KEY(A);

表が変更されました。

では、まずは、普通のSELECT INTO文のループ処理で50万行を1行づつレコード配列に格納しましょう。

時間を計れるようにして

SQL> SET TIMING ON

DBMS_OUTPUTのメッセージを表示可能にして

SQL> SET SERVEROUTPUT ON

実行します。

SQL> DECLARE
  2    /********************************/
  3    -- TEST01表のレコード型配列の型を宣言
  4   /********************************/
  5    TYPE REC_TYPE IS TABLE OF TEST01%ROWTYPE
  6    INDEX BY BINARY_INTEGER;
  7    /********************************/
  8    -- その型を使ってレコード型配列の変数を宣言
  9    /********************************/
 10    REC  REC_TYPE;
 11  BEGIN
 12    /***************************************/
 13    -- ループ処理で 1行づつ、配列に50万レコードを格納
 14    /***************************************/
 15    FOR I IN  1..500000   LOOP
 16        SELECT * INTO REC(I)  FROM TEST01 WHERE A = I;
 17    END LOOP;
 18    /*******************************/
 19    -- 取得できた配列の個数を表示
 20    /*******************************/
 21    DBMS_OUTPUT.PUT_LINE('配列の個数=' || REC.COUNT);
 22  END;
 23  /
配列の個数=500000

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

経過: 00:00:17.12

17秒かかりました。
次は SELECT BULK COLLECT INTO で同じことを一括処理です。

パフォーマンスの比較を正確に行いたいので、一旦メモリをフラッシュします。

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;               ←管理者の権限が必要です

システムが変更されました。

ではバルク・バインドで実行します。

SQL> DECLARE
  2    /********************************/
  3    -- TEST01表のレコード型配列の型を宣言
  4   /********************************/
  5    TYPE REC_TYPE IS TABLE OF TEST01%ROWTYPE
  6    INDEX BY BINARY_INTEGER;
  7    /********************************/
  8    -- その型を使ってレコード型配列の変数を宣言
  9    /********************************/
 10    REC  REC_TYPE;
 11  BEGIN
 12    /***************************************/
 13    -- バルク・バインドで 一括で配列に50万レコードを格納
 14    /***************************************/
 15        SELECT * BULK COLLECT INTO REC  FROM TEST01;
 16    /*******************************/
 17    -- 取得できた配列の個数を表示
 18    /*******************************/
 19    DBMS_OUTPUT.PUT_LINE('配列の個数=' || REC.COUNT);
 20  END;
 21  /
配列の個数=500000

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

経過: 00:00:00.59

今度は約1秒です。

SELECT INTOのループで17秒かった処理が、SELECT BULK COLLECT INTOで一括処理すると1秒です。
これも驚きの速さですね。
ループ処理ではA列を条件に1行づつ問い合わせましたが、A列には主キー索引がありますのでその1行の取得は大変高速です。
それにもかかわらずバルクバインドと比較するとかなり時間がかかるのは、PL/SQLエンジンとSQLエンジンが50万回切り替わることのオーバーヘッドがそれだけ大きいということです。

SELECT BULK COLLECT INTO 文、いかがですか? 
複数行を一括で配列に格納したいときに大変便利ですね。
構文も、その性能もお判りいただけたと思います。

ただ、あえて懸念点をあげると、複数行を一括で配列に格納するということはたくさんのメモリ(PGA)を使うということです。
そこでそんなとき、次回解説するFETCH BULK COLLECT INTO 文を使えば、複数行を小分けにしてFETCHし配列に格納することができます。
例えば、いっぺんに50万行を配列に取得するのではなく、まず1万行を一括でFETCHし配列に格納し何かの処理をする。
それが終われば次の1万行を一括でFETCHし配列に格納し何かの処理をする・・・。
こういったパターンの処理であれば、一度に配列に格納する行数の最大は1万行なのでメモリの消費量を抑えることができます。
それでいて1行づつのループではないので処理も速い。
FETCH BULK COLLECT INTO ならそんなこともできます。

では次回はそのFETCH BULK COLLECT INTO文を解説します。
ご期待ください。

先頭へ戻る