データベースについて調べてみた

データベースについて調べたことのメモ。Oracle要素多めになる予定。

データパッチにおけるUPDATE vs INSERT方式について調べてみた ～ その1 ～

Oracle Database

今回はOracle Databaseネタを。過去に行ったトラブル対応で気になっていたことを調べてみた。

 

今回の調査ネタの背景として、細部で異なる箇所はあるかもしれないが、どんなトラブルだったかを記載しておく。

1.トラブル概要
  • テーブル内の未処理レコードを1件ずつ刈り取って業務処理を行う常駐APがあるような処理モデルで、未処理レコードが大量に残っている状況が明け方に判明
  • 原因は夜間バッチ処理のバグで、本来登録するレコード件数以上のレコード(確か1千万件オーダ)をテーブルに格納してしまったことで、常駐APが刈り取り切れず、未処理レコードが大量に残存
  • オンライン時間帯にもテーブルにレコードが登録されるが、そのレコードは登録されて数分以内に処理済みにしないと、オンライン業務影響が出てしまうので、早急に対処が必要

 

2.復旧方針
  • バグで登録されたレコードを削除したいが、夜間帯に正規に登録されたレコードもテーブルには混在しており、それを選り分けていたらオンラインまでの復旧が間に合わないため、オンライン開放することを優先とし、全レコードを処理済みにパッチする
  • 正規に登録されたレコードで、未処理だったのに処理済みにパッチされたものがあれば、後から未処理に再パッチする(対象量としては極めて少ない想定)
  • 処理済みレコードも別業務から参照されるため、全件TRUNCATEはダメ
 
3.データパッチ方式

1千万件オーダーのレコードに対して、処理済みのフラグをセットするということで、このテーブルに対してUPDATEを実行するのが単純な方法だが、

  • 対象テーブルが年月で分割されたパーティションテーブルで、問題のデータはトラブルが発生した年月に該当するパーティションのみに存在している
  • データパッチが完了するまで、当該テーブルを参照する業務処理を停止できる

という特性があったことから、パッチ対象データが格納されたパーティションと、空テーブルをExchange Partitionで交換した後にINSERTする方法を思いつき、この方法で対応することにした。

 

3.1 UPDATEによるデータパッチ

flg列に1をセットする事で処理済みとなる。パッチ対象のパーティションは特定されているため、パーティションプルーニングを効かせるためにWHERE句で指定(ym = '202302')する。SQLは以下のイメージ。

UPDATE tab01 SET flg = 1 WHERE flg = 0 and ym = '202302';

 

3.2 Exchange Partition + INSERT～SELECTによるデータパッチ

flgを1にUPDATEするのではなく、Exchange Partitionで問題データが格納されたパーティションをテーブル化したもの(tab02)から、空になったパーティションにINSERT～SELECTする際に、flg列部分を1にセットしたレコードをINSERTする。SQLは以下のイメージ

-- Exchange Partition
ALTER TABLE tab01 EXCHANGE PARTITION pt_202302 WITH TABLE tab02
  INCLUDING INDEXES WITHOUT VALIDATION;
  
-- データパッチしながらINSERT
INSERT /*+ APPEND */ INTO tab01 partition(pt_202302) (id, ym, flg, val, memo)
  SELECT id, ym, 1, val, memo FROM tab02;

 

4.調べたかったこと

ようやく本題。データパッチ方式として、Exchange Partition + INSERT～SELECTを選んだのは、

  • Oracle Databaseではデータ更新時(INSERT/UPDATE/DELETEなど)に、更新前の情報(データブロックイメージ)をUNDOデータとして生成するので、更新前のデータが存在するUPDATEの方がUNDOデータが大きくなる分、遅くなる気がした。
  • UPDATEだと1レコード毎に条件に合致するかの判定処理が発生する一方で、INSERTは判定させずにフラグをセット(flg = 1)する分早い気がした。
    ※条件なしでUPDATEさせたら判定は無くなるかもしれないが、その分UNDOが増えて、やっぱり遅くなるのではないかと想定
  • INSERTの場合、ダイレクトパスインサートが使えるので、バッファキャッシュアクセス数が減る分、UPDATEより早い気がした。

など、色々メリットがあると思ったからなのだが、本当にそうなのかなぁという気持ちも少しあったので、簡易的な検証を行って、想定していたメリットが本当にあったのか調べてみることにした。

 

背景の説明が長くなったので、検証結果は次回に。

 

 

前回の記事：グラフDBについて調べてみた