読者です 読者をやめる 読者になる 読者になる

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】大量レコードのDeleteが遅いので改善したい。

はじめに

Oracleのdelete文は遅いというのが通説ですが、本当に遅いのか気になったので検証してみました。
PL/SQLで100万件のレコードを用意してdeleteしてみました。


2つの方式
  1. ある条件でフェッチしてヒットしたレコードを1つずつ削除する
  2. ある条件でdeleteを繰り返し、レコードがなくなったら終了する。

どちらの方式も1000レコード削除した時点でコミットすること。
やる前から2の方式の方が、無駄がなくて速い予感。

フェッチして削除する(方式1)
set serveroutput on;
declare
  vNum NUMBER; --ループカウンタ
  t1 Table01%rowtype;
  -- 'A'='A'としているためすべてのレコードが無条件ヒットする
  cursor cuTest1 is select * from Table01 where 'A' = 'A' order by id asc;
begin
  vNum := 1;
  -- カーソルオープン
  open cuTest1;
  
  loop
    fetch cuTest1 into t1;
    -- データがなくなったらexit
    exit when cuTest1%notfound;
    
    --レコードを削除
    delete from table01 where id = t1.id;
    vNum := vNum + 1;
    
    --1000件消したらコミットする
    IF (MOD(vNum, 1000) = 0) THEN
      commit;
    END IF;
    
  end loop;
  -- カーソルクローズ
  close cuTest1;
end;
/
ある程度まとめてdeleteする(方式2)
set serveroutput on;
declare
  vNum NUMBER;
  vDelCount NUMBER;
begin
  vNum := 0;
  vDelCount :=0;
  
  loop

    --レコードを削除 -- 'A'='A'としているためすべてのレコードが無条件ヒットする
    delete from table01 where 'A' = 'A' and rownum <= 1000;
    vDelCount := SQL%ROWCOUNT;
    dbms_output.put_line (vDelCount);
    commit;
    IF vDelCount = 0 THEN
      exit;
    END IF;
    vNum := vNum + vDelCount;
    
  end loop;
  dbms_output.put_line (vNum || '件削除しました');

end;
/
性能差の比較

1万からデータ数を10倍、100倍としていき、3回試行した結果を平均しました。
1万件くらいでは方式の差はでませんが、データ数が多くなってくると差が現れてきて、方式2のまとめてdeleteのほうが2倍くらい速くなります。
f:id:replication:20150131214920p:plain

データ数 fetch-delete(秒) まとめてdelete(秒)
1万件 0.932 0.167
10万件 10.130 4.370
100万件 95.284 52.572
まとめ

Oracleのdeleteが遅い場合、処理ロジックを見直し、無駄なフェッチを繰り返していないか確認すると良いかもしれません。