SQL:大量のレコードを安全にdeleteしたい

背景と状況

他システムに情報を送る際、組み立てた通信伝文を一旦データベースのテーブルに保管してから送信、成功したらレコードに送信済みフラグを立てて完了というやり方をよくします。一定期間を過ぎるとこの送信テーブルのレコードは不要になりますが往々にして消されず残ったままになってることがあります。数年もするとテーブルスペースを圧迫したり、パフォーマンスが落ちる場合があるので古いレコードはdeleteしたいです。

やりたいこと

送信テーブル(snd_tbl)のレコードを直近2ヶ月分を残してあとは削除したい。要はSQLでdeleteを大量のレコードにしたい。

snd_tbl

C:\>db2 select * from snd_tbl

SND_ID               SND_TIMESTAMP              SND_FLG SND_TEXT
-------------------- -------------------------- ------- -----------------
                   1 2017-09-04-18.53.51.992000 1       ddddd
                   2 2018-09-04-18.53.51.992000 1       eeeee
                   3 2019-09-04-18.53.51.992000 1       fffff
                   4 2020-07-01-18.53.51.992000 1       hhhhh
                   5 2020-08-05-18.53.51.992000 1       iiiii
                   6 2020-09-04-18.53.51.998000 0       jjjjj

  6 レコードが選択されました。

C:\>date /t
2020/09/04

※IBM Db2 V11.1 Windowsで確認していますが他のDBMSでもできるはずです。

制約条件

• テーブルは本番環境で常にアクセスされる
• 本番環境を止めずに作業を行う必要がある
• テーブル設計は変更できない

問題点

例は6レコードですが1日100件を送信したとして、5年だと18万2500レコードになります。これを単純に2ヶ月より前のレコードを削除しようとすると最悪の場合、システムが停止します。

単純に削除するSQL

delete from snd_tbl where date(SND_TIMESTAMP) < current date - 2 month ;

原因は大量レコードを1度に削除しようとすると、アクティブログがフルになったり、ロックエスカレーションによりテーブル全体がロックされて、アプリケーションがテーブルにアクセスできなくなるからです。これは更新の場合も同様です。

対策

where句で範囲を狭めて削除します。例ですと日ごとにレコードを削除します。

Where句で範囲を狭めたSQL

delete from snd_tbl where date(SND_TIMESTAMP) = '2017-09-04' ;

ただし、5年分もあるとSQLを手書きするのはつらいのでなんとかしたいと思います。

SQLを作るSQL

送信テーブルのタイムスタンプ列に対してgroup byするSQLを実行してSQLを生成します。

make_delete_snd_tbl.sql

SELECT 'delete from snd_tbl where date(SND_TIMESTAMP) = ''' 
       || DATE(snd_timestamp) 
       || ''' ;' 
FROM   snd_tbl 
WHERE  DATE(snd_timestamp) < CURRENT DATE - 2 MONTH 
GROUP  BY DATE(snd_timestamp) 
ORDER  BY DATE(snd_timestamp) 
WITH UR; 

実行結果を標準出力に出して編集するか、Db2なら以下のコマンドできれいに出力されます。
db2 -txf make_delete_snd_tbl.sql -z delete_snd_tbl.sql
以下のSQLが生成されます。

delete_snd_tbl.sql

delete from snd_tbl where date(SND_TIMESTAMP) = '2017-09-04' ;
delete from snd_tbl where date(SND_TIMESTAMP) = '2018-09-04' ;
delete from snd_tbl where date(SND_TIMESTAMP) = '2019-09-04' ;
delete from snd_tbl where date(SND_TIMESTAMP) = '2020-07-01' ;

生成されたSQLを実行することで日ごとにレコードを削除します。

実行結果

C:\>db2 -tvf delete_snd_tbl
delete from snd_tbl where date(SND_TIMESTAMP) = '2017-09-04'
DB20000I  SQL コマンドが正常に完了しました。
delete from snd_tbl where date(SND_TIMESTAMP) = '2018-09-04'
DB20000I  SQL コマンドが正常に完了しました。
delete from snd_tbl where date(SND_TIMESTAMP) = '2019-09-04'
DB20000I  SQL コマンドが正常に完了しました。
delete from snd_tbl where date(SND_TIMESTAMP) = '2020-07-01'
DB20000I  SQL コマンドが正常に完了しました。

まとめ

• 大量レコードを削除するときは範囲指定してSQLを複数に分けます
• group by を使ってSQLを生成します
• こんなSQLを流さなくて済むように定期的に自動削除するしくみを作りましょう