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

はじめに

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

オラクルマスター教科書 Bronze Oracle Database 12c SQL基礎

posted with ヨメレバ

株式会社システム・テクノロジー・アイ林優子,桜井裕実翔泳社 2014-08-29

Amazon

Kindle

２つの方式

ある条件でフェッチしてヒットしたレコードを１つずつ削除する
ある条件で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;
/