PSI Labs RSS feed

Oracle DBでの大量データ(数億レコード)のUPDATEの手法

こんにちは。shintaniです。

今回は超大量データを一括UPDATEする場合の手法を紹介します。
※多少改造すればDELETEでも使えます


テーブル内の全データをUPDATEする場合、単純に書くと下記のようなSQLとなります。

UPDATE  TBL_TEST_TABLE
  SET
     COL1 = '01234567890123456789'

レコード数が少なければこのSQLで問題ありません。

しかし、数億レコードのような大量のレコードが入っている場合は上手くいきません。
長時間掛かった挙句、UNDO表領域を使い果たして
「ORA-30036: (UNDO表領域'XXXXXX'内)でセグメントを拡張できません」
というエラーが返ってくることがあります。

このような場合は下記のようなPL/SQLにすると、汎用的に色々なテーブルで使いまわせ、
かつ実行時間も短くて済みます。

set serveroutput on

DECLARE
    CURSOR c1 IS SELECT rowid c_rowid FROM TBL_TEST_TABLE;   -- ***
    v_rowid dbms_sql.urowid_table;
    v_update_count  NUMBER(20) := 0;
BEGIN
    OPEN c1;
        LOOP
            FETCH c1 BULK COLLECT INTO v_rowid LIMIT 10000;    -- 1万レコード単位でコミット
            EXIT WHEN v_rowid.count = 0;

            FORALL i IN 1..v_rowid.count
                UPDATE TBL_TEST_TABLE   -- ***
                  SET
                     COL1 = '01234567890123456789'    -- ***
                    WHERE rowid = v_rowid(i);

            v_update_count := v_update_count + SQL%ROWCOUNT;
            COMMIT;
        END LOOP;
    CLOSE c1;

    DBMS_OUTPUT.PUT_LINE('UPATE件数 = ' || v_update_count);
END;
/

全体としては大きくなりましたが、個々のSQLで修正する箇所は *** を付けた箇所のみです。後はそのまま使えます。

このPL/SQLはBULK COLLECTという手法を使っています。
rowid で1レコードずつUPDATEするより、こちらの方が遥かに早いです。


単純なUPDATEと、上記のBULK COLLECTの手法の処理時間を比較してみました。

【DB全体の設定】
・UNDO表領域 15G。 自動拡張OFF
・sga_max_size - 8G

【テーブルの設定】
・約50列。
・2列に対して更新。
・インデックスは主キー1つのみ(UPDATEの対象列ではない。もし対象の場合はインデックスを一時的に無効化しないとトンデモない時間が掛かります。)


・100万レコード
 (テーブルのセグメントサイズ:350M)

単純UPDATE - 10秒
BULK COLLECT - 10秒


1000万レコード
 (テーブルのセグメントサイズ:3.5G)

単純UPDATE - 10分52秒
BULK COLLECT - 13分10秒


2000万レコード
 (テーブルのセグメントサイズ:6.8G)

単純UPDATE - 37分40秒
BULK COLLECT - 35分 7秒


1億レコード
 (テーブルのセグメントサイズ:34G)

単純UPDATE - 2時間半後にORA-30036エラー
BULK COLLECT - 3時間29分


レコード数と時間が単純な比例となっていないのは、SGAにデータバッファを溜めておける量が影響していると思われます。
(処理が終了した後もバッファキャッシュからディスクへ書き込みしていると思われるI/Oが10分近く発生。)

最後の1億レコードでは単純UPDATEではエラーが起こってしまいましたが、
BULK COLLECT ならばエラーは起こらず、かつ単純UPDATEが成功する場合と遜色無い時間で完了しました。

このような超大量データのUPDATEで苦労されている方は、どうぞお試し下さい。

ではでは。