カーソルのループでINSERT100万とSELECT INSERT100万の速度比較してみた

やるまえから結果が見えてる試みではあるんですが。最近SQLを再勉強するにあたり、SQLてのは、手続き的にループ回すのに比べて集合to集合の演算の方が圧倒的に早い、というのを改めて認識した。なので、このエントリはそれを実感するのが目的です。

やったこと

環境はOracle 11g XEを使用。

FROM側とTO側のデータを入れるテーブルを作る。FROM側には100万件レコードを入れておく。

CREATE TABLE FROM_A_TBL (CLM VARCHAR2(16));
CREATE TABLE TO_A_TBL   (CLM VARCHAR2(16));

FROMテーブルをカーソルでループしながらTOテーブルに1件ずつINSERTするPL/SQLを作る。

create or replace
PROCEDURE TO_WITH_CURSOR
IS
CURSOR C1 IS
    SELECT CLM FROM FROM_A_TBL;
BEGIN
    FOR X IN C1 LOOP
        INSERT INTO TO_A_TBL VALUES (X.CLM);
    END LOOP;
    COMMIT;
END TO_WITH_CURSOR;

INSERT-SELECTするPL/SQLを作る（べつにPL/SQLでなくともいいんだけどまぁなんとなく）

create or replace
PROCEDURE TO_WITH_SELECTINSERT AS 
BEGIN
  INSERT INTO TO_A_TBL SELECT CLM FROM FROM_A_TBL;
  COMMIT;
END TO_WITH_SELECTINSERT;

どっちのやり方もTO側のテーブルに100万件作られることに変わりはない。下記はチェック用のクエリ（2つのテーブルがマッタク同一な検査はこれじゃ不十分だけどよしとしておく）。

SELECT * FROM FROM_A_TBL
MINUS
SELECT * FROM TO_A_TBL;

それぞれのPL/SQLを流す前に、TO_A_TBLのDROP＆CREATE、Oracleインスタンス再起動を毎回する。一応、キャッシュとかの影響はなるべく排除したいので。

で、結果。

秒数	１	２	３
FORLOOP INSERT	40	40	40
INSERT SELECT	3	3	3

はい。10倍くらい違うらしー。

2012/06/14 追記

Oracleのダイレクト・パス・インサートという機能の存在を知ったので試してみた。

INSERT /*+ APPEND */ INTO  TO_A_TBL SELECT CLM FROM FROM_A_TBL;
COMMIT;

1秒かかるかかからないかってくらいほぼ一瞬で終了する。

以下おまけ。

下記のような書き方はいけるんすかね、と試したがコレは ORA-12838 が出る。

create or replace
PROCEDURE TO_WITH_CURSOR_D 
IS
CURSOR C1 IS
    SELECT CLM FROM FROM_A_TBL;
BEGIN
    FOR X IN C1 LOOP
        INSERT  /*+ APPEND_VALUES */ INTO TO_A_TBL VALUES (X.CLM) ;
    END LOOP;
    COMMIT;
END TO_WITH_CURSOR_D;

ダイレクトロードインサート処理の実行直後は、必ずトランザクションを完了させる必要がありますからといって、下記のようなクエリにすると何時までたっても終わらないクエリになる。

    FOR X IN C1 LOOP
        INSERT  /*+ APPEND_VALUES */ INTO TO_A_TBL VALUES (X.CLM) ;
        COMMIT;
    END LOOP;

参考URLを読む限り、ダイレクト・パス・インサートはINSERTを早くしたいというだけの理由で使えるものではない。データを追加するというより、テーブルへの初期データ投入向け機能って感じですかね。

参考URL

ダイレクト・パス・インサート - オラクル・Oracleをマスターするための基本と仕組み

Oracleクロニクル ダイレクト・パス・インサートと削除を繰り返すと、最高水位標(HWM)が上昇して最後にどうなる？

2012/06/25 追記

Oracleのバルクインサートという機能の存在を知ったので試してみた。

バルクインサート - オラクル・Oracle PL/SQL 入門をほぼコピペしたPL/SQLを作成する。FROMテーブルをカーソルでループしながらTOテーブルにINSERTしていくが、4件ずつのバルクインサートに変更している。

create or replace
PROCEDURE TO_WITH_CURSOR_BULKINSERT 
AS 
  CURSOR C1 IS SELECT CLM FROM FROM_A_TBL;
  TYPE tIDNAMES IS TABLE OF FROM_A_TBL%ROWTYPE INDEX BY BINARY_INTEGER;
  vIDName tIDNAMES;
  
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT INTO vIDName LIMIT 4;
    EXIT WHEN vIDName.COUNT = 0;
    BEGIN
      FORALL i IN 1..vIDName.COUNT
        INSERT INTO TO_A_TBL VALUES vIDName(i);
    END;
    
  END LOOP;
  COMMIT;
END TO_WITH_CURSOR_BULKINSERT;

で、結果。

秒数	１	２	３
FORLOOPバルクインサート	10	10	10

1件ずつに比べればかなり速くなる。ちなみに、バルクインサート一回の件数を増やせばさらに速くなった。

参考URL

バルクインサート - オラクル・Oracle PL/SQL 入門
Oracle クロニクル : PL/SQLから大量のコレクション型配列データをとにかく高速にINSERTしてみる