タイトルロゴ

記載日 2006/02/26
はじめに

 PL/SQLでは、様々なデータ加工処理が行われます。 通常のデータ加工の流れは、

    「データ検索」
      ↓
    「検索データの加工」
      ↓
    「加工したデータの挿入」


と言う流れになるのではないでしょうか。「データ検索」がないのであれば、わざわざPL/SQLで データ加工する必要はないでしょう。

 大規模データベースで大量のデータをPL/SQLで加工しているシステムがあるとします。この場合、 データ加工結果をコレクション型 と呼ばれる配列に格納して、処理の最後にまとめてデータ挿入する手法が取られます。

 ここでは、大量のデータをテーブルに挿入する場合の高速化手段について検証してみます。



セクション













検証環境

 
ページのTOPへ

 PL/SQLにおいて、コレクション型の配列に作成したデータをテーブルに挿入する場合、 どのような実装方法が考えられるでしょうか。考えられる実装方法を四つあげてみます。

  ■ 1-1) FORループによって配列のデータを一件ずつINSERTする
 コレクション型の配列のサイズを取得し、そのサイズ数分ループしながらINSERT文を発行する と言う最もオーソドックスな実装方法です。しかし、この手段は、Oracle8iから使用可能になったバルクバインド により、ほとんど見かけなくなりました。


    FOR i IN (配列初期番地)..(配列最終番地) LOOP
        INSERT INTO テーブル VALUES(配列(i));
    END LOOP;

    COMMIT;

  ■ 1-2) FORALLによるバルクバインドを利用して、配列データを一括でINSERTする
 FORALLによるバルクバインドはOracle8iから使用できるようになった機能です。 配列をループせずに処理するため、高速なインサート手段として有名です。 最近では、 1-1) のループしながらINSERT文を発行する手段に取って代わって使用される 頻度は上がっています。


    FORALL i (配列初期番地)..(配列最終番地)
    INSERT INTO テーブル VALUES(配列(i));

    COMMIT;

  ■ 1-3) ダイレクト・パス・インサートをDUAL表を利用して行う
 ダイレクト・パス・インサートは、

コンテンツ:

でも紹介しましたが、テーブル間のデータをデータベースバッファキャッシュを介さずに直接データファイルにINSERTを 行うことで高速処理する手段です。

 ここでは、FORループによって配列のデータを一件ずつDUAL表 から検索し、ダイレクト・パス・インサートを実現します。ダイレクト・パス・インサートについて詳しく 知らない場合は、上のコンテンツを参照してから、こちらのコンテンツに戻ってきて下さい。

 この方法のポイントは、DUAL表を用いたSELECTを用いてダイレクト・パス・インサートを行い、その都度 トランザクション確定としてCOMMITが必要になる点です。


    FOR i IN (配列初期番地)..(配列最終番地) LOOP
        INSERT /*+ APPEND */ INTO テーブル
            (SELECT 配列(i).要素1, 配列(i).要素2,...配列(i).要素n, FROM DUAL);
        COMMIT;  --// 一行インサートする毎にコミット
    END LOOP;


  ■ 1-4) ダイレクト・パス・インサートを表関数を利用して一括で行う
 Oracleでは、コレクション型を戻り値とする表関数を作成することができます。表関数については、

コンテンツ:

でも紹介しました。配列を表関数の戻り値とすることで、SELECT文で配列を取出すことが可能になります。 この表関数のSELECT文をダイレクト・パス・インサートに利用して、一括でINSERTします。


    INSERT /*+ APPEND */ INTO テーブル
        (SELECT * FROM TABLE(表関数配列(i)));

    COMMIT;

 データのインサート先のテーブルとして以下を用意することとしました。

データ挿入テストテーブル
 No.   COLUMN   TYPE   PK   IX   NL   説明 
 1  アルファベット CHAR(21) 文字列型データ。
 2  数字 NUMBER(10) 数字型データ。
 3  日付 DATE 日付型データ。

 このテーブルのCREATE TABLE文は以下になります。
CREATE TABLE  ← こちらをクリック(※使用する場合は、表領域名を実行環境に合わせて書換える必要があります。)

/* -------------------------------- */
/* テーブル定義                     */
/* -------------------------------- */
/* データ挿入テストテーブル */
CREATE TABLE INSERT_TEST_T(
      CHAR_COLUMN  CHAR(21)
    , NUMBER_COLUMN  NUMBER(10)
    , DATE_COLUMN  DATE
) TABLESPACE 表領域名1;


 
ページのTOPへ

 次のパッケージを用意します。内部で十万件のデータを作成し、 そのデータを全てテーブルにインサートします。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* インサートテストパッケージ定義   */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLINSERTTEST_01_P
AS
    /* ------------------------------------------ */
    /* RECORD型の定義                             */
    /* ------------------------------------------ */
    TYPE TYPE_INSERT_TEST_T IS RECORD(
          CHAR_COLUMN  CHAR(21)
        , NUMBER_COLUMN  NUMBER(10)
        , DATE_COLUMN  DATE
    );
    TYPE TBLTYPE_INSERT_TEST_T IS TABLE OF TYPE_INSERT_TEST_T
        INDEX BY BINARY_INTEGER;

    /* ------------------------------------------ */
    /* 配列の定義                                 */
    /* ------------------------------------------ */
    TEST_DATA TBLTYPE_INSERT_TEST_T;

    /* ------------------------------------------ */
    /* FORループ配列INSERTプロシジャー            */
    /* 引 数:無し                               */
    /* 戻り値:無し                               */
    /* 説 明:内部でコレクション型の配列を作成し */
    /*         FORループによってデータを一件ずつ  */
    /*         テーブルにINSERTする。             */
    /* ------------------------------------------ */
    PROCEDURE INSERT_TEST;

END PLSQLINSERTTEST_01_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLINSERTTEST_01_P
AS

    /* ------------------------------------------ */
    /* FORループ配列INSERTプロシジャー            */
    /* 引 数:無し                               */
    /* 戻り値:無し                               */
    /* 説 明:内部でコレクション型の配列を作成し */
    /*         FORループによってデータを一件ずつ  */
    /*         テーブルにINSERTする。             */
    /* ------------------------------------------ */
    PROCEDURE INSERT_TEST AS
    BEGIN
        /* DBMS_OUTPUTパッケージを使用することを明記。 */
        DBMS_OUTPUT.ENABLE;

        /* テストデータを100000件作成 */
        FOR IDX IN 1..100000 LOOP
            TEST_DATA(IDX).CHAR_COLUMN := 'ABCDEFGHIJKLMNOPQRSTU';
            TEST_DATA(IDX).NUMBER_COLUMN := 1234567890;
            TEST_DATA(IDX).DATE_COLUMN := SYSDATE;
        END LOOP;

        /* INSERT開始時間 */
        DBMS_OUTPUT.PUT_LINE('開始 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

        /* テストデータをテーブルに挿入 */
        FOR IDX IN TEST_DATA.FIRST..TEST_DATA.LAST LOOP
            INSERT INTO INSERT_TEST_T
            VALUES(   TEST_DATA(IDX).CHAR_COLUMN
                    , TEST_DATA(IDX).NUMBER_COLUMN
                    , TEST_DATA(IDX).DATE_COLUMN);
        END LOOP;

        COMMIT;

        /* INSERT終了時間 */
        DBMS_OUTPUT.PUT_LINE('終了 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

    END INSERT_TEST;
END PLSQLINSERTTEST_01_P;
/

 上のパッケージの処理時間を測定します。全部で五回実施します。 五回の計測条件ができるだけ同じになるように、毎回実行する前に、INSERT_TEST_Tのデータを TRUNCATE TABLEしました。

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:03.95 00:00:03.87 00:00:04.92 00:00:03.82 00:00:04.15 00:00:04.142

 この測定結果が、この後に続く測定結果の基準になります。


 
ページのTOPへ

 次のパッケージを用意します。内部で十万件のデータを作成し、 そのデータを全てFORALLバルクバインドでテーブルにインサートします。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* インサートテストパッケージ定義   */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLINSERTTEST_02_P
AS
    /* ------------------------------------------ */
    /* RECORD型の定義                             */
    /* ------------------------------------------ */
    TYPE TYPE_INSERT_TEST_T IS RECORD(
          CHAR_COLUMN  CHAR(21)
        , NUMBER_COLUMN  NUMBER(10)
        , DATE_COLUMN  DATE
    );
    TYPE TBLTYPE_INSERT_TEST_T IS TABLE OF TYPE_INSERT_TEST_T
        INDEX BY BINARY_INTEGER;

    /* ------------------------------------------ */
    /* 配列の定義                                 */
    /* ------------------------------------------ */
    TEST_DATA TBLTYPE_INSERT_TEST_T;

    /* ------------------------------------------ */
    /* FORALLバルクバインドINSERTプロシジャー     */
    /* 引 数:無し                               */
    /* 戻り値:無し                               */
    /* 説 明:内部でコレクション型の配列を作成し */
    /*         FORALLバルクバインドによってデータ */
    /*         を一括INSERTする。                 */
    /* ------------------------------------------ */
    PROCEDURE INSERT_TEST;

END PLSQLINSERTTEST_02_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLINSERTTEST_02_P
AS

    /* ------------------------------------------ */
    /* FORALLバルクバインドINSERTプロシジャー     */
    /* 引 数:無し                               */
    /* 戻り値:無し                               */
    /* 説 明:内部でコレクション型の配列を作成し */
    /*         FORALLバルクバインドによってデータ */
    /*         を一括INSERTする。                 */
    /* ------------------------------------------ */
    PROCEDURE INSERT_TEST AS
    BEGIN
        /* DBMS_OUTPUTパッケージを使用することを明記。 */
        DBMS_OUTPUT.ENABLE;

        /* テストデータを100000件作成 */
        FOR IDX IN 1..100000 LOOP
            TEST_DATA(IDX).CHAR_COLUMN := 'ABCDEFGHIJKLMNOPQRSTU';
            TEST_DATA(IDX).NUMBER_COLUMN := 1234567890;
            TEST_DATA(IDX).DATE_COLUMN := SYSDATE;
        END LOOP;

        /* INSERT開始時間 */
        DBMS_OUTPUT.PUT_LINE('開始 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

        /* テストデータをテーブルに挿入 */
        FORALL IDX IN TEST_DATA.FIRST..TEST_DATA.LAST
        INSERT INTO INSERT_TEST_T
        VALUES TEST_DATA(IDX);

        COMMIT;

        /* INSERT終了時間 */
        DBMS_OUTPUT.PUT_LINE('終了 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

    END INSERT_TEST;
END PLSQLINSERTTEST_02_P;
/

 上のパッケージの処理時間を測定します。全部で五回実施します。 五回の計測条件ができるだけ同じになるように、毎回実行する前に、INSERT_TEST_Tのデータを TRUNCATE TABLEしました。

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:00.42 00:00:00.61 00:00:00.42 00:00:00.47 00:00:00.43 00:00:00.47

 「§2) FORループによって配列のデータを一件ずつINSERTする」に比べて、 圧倒的に高速に処理しました。

 
ページのTOPへ

 次のパッケージを用意します。内部で十万件のデータを作成し、 そのデータを全てDUAL表を用いてダイレクト・パス・インサートします。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* インサートテストパッケージ定義   */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLINSERTTEST_03_P
AS
    /* ------------------------------------------ */
    /* RECORD型の定義                             */
    /* ------------------------------------------ */
    TYPE TYPE_INSERT_TEST_T IS RECORD(
          CHAR_COLUMN  CHAR(21)
        , NUMBER_COLUMN  NUMBER(10)
        , DATE_COLUMN  DATE
    );
    TYPE TBLTYPE_INSERT_TEST_T IS TABLE OF TYPE_INSERT_TEST_T
        INDEX BY BINARY_INTEGER;

    /* ------------------------------------------ */
    /* 配列の定義                                 */
    /* ------------------------------------------ */
    TEST_DATA TBLTYPE_INSERT_TEST_T;

    /* ----------------------------------------------------------- */
    /* FORループ配列ダイレクト・パス・インサートプロシジャー       */
    /* 引 数:無し                                                */
    /* 戻り値:無し                                                */
    /* 説 明:内部でコレクション型の配列を作成しFORループによっ   */
    /*         て配列データをDUAL表で一件ずつSLECTしてテーブルに   */
    /*         INSERTする。                                        */
    /* ----------------------------------------------------------- */
    PROCEDURE INSERT_TEST;

END PLSQLINSERTTEST_03_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLINSERTTEST_03_P
AS

    /* ----------------------------------------------------------- */
    /* FORループ配列ダイレクト・パス・インサートプロシジャー       */
    /* 引 数:無し                                                */
    /* 戻り値:無し                                                */
    /* 説 明:内部でコレクション型の配列を作成しFORループによっ   */
    /*         て配列データをDUAL表で一件ずつSLECTしてテーブルに   */
    /*         INSERTする。                                        */
    /* ----------------------------------------------------------- */
    PROCEDURE INSERT_TEST AS
    BEGIN
        /* DBMS_OUTPUTパッケージを使用することを明記。 */
        DBMS_OUTPUT.ENABLE;

        /* テストデータを100000件作成 */
        FOR IDX IN 1..100000 LOOP
            TEST_DATA(IDX).CHAR_COLUMN := 'ABCDEFGHIJKLMNOPQRSTU';
            TEST_DATA(IDX).NUMBER_COLUMN := 1234567890;
            TEST_DATA(IDX).DATE_COLUMN := SYSDATE;
        END LOOP;

        /* INSERT開始時間 */
        DBMS_OUTPUT.PUT_LINE('開始 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

        /* テストデータをテーブルに挿入 */
        FOR IDX IN TEST_DATA.FIRST..TEST_DATA.LAST LOOP
            INSERT /*+ APPEND */ INTO INSERT_TEST_T
            SELECT
                  TEST_DATA(IDX).CHAR_COLUMN
                , TEST_DATA(IDX).NUMBER_COLUMN
                , TEST_DATA(IDX).DATE_COLUMN
            FROM DUAL;
            COMMIT;
        END LOOP;

        /* INSERT終了時間 */
        DBMS_OUTPUT.PUT_LINE('終了 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

    END INSERT_TEST;
END PLSQLINSERTTEST_03_P;
/

 上のパッケージの処理時間を測定します。全部で五回実施します。 五回の計測条件ができるだけ同じになるように、毎回実行する前に、INSERT_TEST_Tのデータを TRUNCATE TABLEしました。

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:14.93 00:00:14.79 00:00:14.43 00:00:15.18 00:00:14.50 00:00:14.766

 「§2) FORループによって配列のデータを一件ずつINSERTする」に比べて、 かなり処理速度が遅いです。ダイレクト・パス・インサート はバッファキャッシュを介さないので高速だと言っても、毎回COMMITを実行していれば、 それは処理速度に影響するでしょう。 また、この方法は必要以上に表領域を使用します。理由は、 ダイレクト・パス・インサートは最高水位標(HWM)より後ろにデータを 挿入するからです。最高水位標(HWM) の指すブロックに、まだデータが入る余地があっても、次の後ろのブロックにデータを入れると いうことです。苦労してダイレクト・パス・インサートを試すだけ 無駄です。素直にFORALLによるバルクバインドを使用しましょう。


 
ページのTOPへ

 Oracleには、ネスト表を戻り値とする表関数を作成することができます。 ネスト表とは、PL/SQLで使用する配列の一種です。

 まずは、レコードをイメージしたCHAR型とNUMBER型、DATE型のオブジェクト型を作成 し、その型を用いたネスト表を宣言します。 CREATE TYPE文は、以下のようになります。
CREATE TYPE  ← こちらをクリック

/* -------------------------------------------- */
/* レコードをイメージしたオブジェクトタイプ定義 */
/* -------------------------------------------- */
CREATE OR REPLACE TYPE TYPE_INSERT_TEST_OBJ IS OBJECT ( C CHAR(21), N NUMBER, D DATE );
/
/* -------------------------------------------- */
/* TYPE_INSERT_TEST_OBJ型のネスト表定義         */
/* -------------------------------------------- */
CREATE OR REPLACE TYPE TBLTYPE_INSERT_TEST IS TABLE OF TYPE_INSERT_TEST_OBJ;
/

 次のパッケージを用意します。内部で十万件のデータを作成し、 そのデータを全てDUAL表を用いてダイレクト・パス・インサートします。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* インサートテストパッケージ定義   */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLINSERTTEST_04_P
AS
    /* ------------------------------------------ */
    /* 配列の定義                                 */
    /* ------------------------------------------ */
    TEST_DATA TBLTYPE_INSERT_TEST;

    /* ------------------------------------------ */
    /* TEST_DATAを返す表関数の定義                */
    /* ------------------------------------------ */
    FUNCTION GET_TEST_DATA(ARR TBLTYPE_INSERT_TEST)
    RETURN TBLTYPE_INSERT_TEST;
    
    /* ---------------------------------------------- */
    /* 一括ダイレクト・パス・インサートプロシジャー   */
    /* 引 数:無し                                   */
    /* 戻り値:無し                                   */
    /* 説 明:内部でコレクション型の配列を作成し、表 */
    /*         関数を用いて配列のデータをSELECTして、 */
    /*         一括ダイレクト・パス・インサートを行い */
    /*         ます。                                 */
    /* ---------------------------------------------- */
    PROCEDURE INSERT_TEST;

END PLSQLINSERTTEST_04_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLINSERTTEST_04_P
AS
    /* ------------------------------------------ */
    /* TEST_DATAを返す表関数の定義                */
    /* ------------------------------------------ */
    FUNCTION GET_TEST_DATA(ARR TBLTYPE_INSERT_TEST)
    RETURN TBLTYPE_INSERT_TEST
    AS
    BEGIN
        RETURN ARR;
    END GET_TEST_DATA;

    /* ---------------------------------------------- */
    /* 一括ダイレクト・パス・インサートプロシジャー   */
    /* 引 数:無し                                   */
    /* 戻り値:無し                                   */
    /* 説 明:内部でコレクション型の配列を作成し、表 */
    /*         関数を用いて配列のデータをSELECTして、 */
    /*         一括ダイレクト・パス・インサートを行い */
    /*         ます。                                 */
    /* ---------------------------------------------- */
    PROCEDURE INSERT_TEST AS
        /* ネスト表の初期化*/
        TEST_DATA TBLTYPE_INSERT_TEST
            := TBLTYPE_INSERT_TEST(TYPE_INSERT_TEST_OBJ(null, null, null));
    BEGIN
        /* DBMS_OUTPUTパッケージを使用することを明記。 */
        DBMS_OUTPUT.ENABLE;

        /* テストデータを100000件作成 */
        TEST_DATA.EXTEND(100000);
        FOR IDX IN 1..100000 LOOP
            TEST_DATA(IDX) := TYPE_INSERT_TEST_OBJ(
                                                  'ABCDEFGHIJKLMNOPQRSTU'
                                                , 1234567890
                                                , SYSDATE
                                            );
        END LOOP;

        /* INSERT開始時間 */
        DBMS_OUTPUT.PUT_LINE('開始 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

        /* テストデータをテーブルに挿入 */
        INSERT /*+ APPEND */ INTO INSERT_TEST_T
        SELECT * FROM TABLE(PLSQLINSERTTEST_04_P.GET_TEST_DATA(TEST_DATA));
        COMMIT;

        /* INSERT終了時間 */
        DBMS_OUTPUT.PUT_LINE('終了 --> ' || TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF'));

    END INSERT_TEST;
END PLSQLINSERTTEST_04_P;
/

 上のパッケージの処理時間を測定します。全部で五回実施します。 五回の計測条件ができるだけ同じになるように、毎回実行する前に、INSERT_TEST_Tのデータを TRUNCATE TABLEしました。

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:02.67 00:00:02.59 00:00:02.64 00:00:02.48 00:00:02.62 00:00:02.60

 「§2) FORループによって配列のデータを一件ずつINSERTする」に比べれば 高速ですが、「§3) FORALLによるバルクバインドを利用して、配列データを一括でINSERTする 」 にはかないません。

 
ページのTOPへ
 大量のコレクション型のデータをテーブルにINSERTするには、FOARLLバルクバインドによる INSERTがもっとも効果的です。 同じく、大量のデータを高速にインサートする手段としてダイレクト・パス・インサート がありますが、これは、

INSERT /*+ APPEND */ INTO テーブル ( SELECT ... FROM テーブル ...)

と言う形で使用すべきであり、コレクション型のデータを無理矢理ダイレクト・パス・インサート にすると、骨折り損のくたびれもうけです。