インデックスのついた列をUPDATEしまくるとインデックスのサイズは増えまくる

インデックスをタクサン作ったときINSERT,UPDATE,DELETEは遅くなるか - kagamihogeのblog を書いたあと、下記の本をあらためて見直したところ、このような記述となっていた。

データを削除するとテーブルの行は物理的に削除されるが、インデックスの行は単に削除されたことを表す表示（Flag）が追加されるだけである。
（中略）
インデックス列を構成している列の値が更新されると、削除後に挿入が発生する。内部的には、ユーザーが削除、挿入という作業を順に行った場合と同じ作業が実行される。
データベースパフォーマンスアップの教科書基本原理編第2章インデックスの種類と特徴 2.1.2 B-Treeインデックスの操作（Operation） 3）データ削除および更新 p.53 より抜粋

なお、この本は実装はOracleを前提に書かれている。てなわけで、インデックスつけたテーブルをUPDATEしまくって実際に何が起きるのかを確かめてみる。

環境

Oracle Database Express Edition 11g Release 2をCentOS 6.4 x86_64上で動作
Oracle SQL Developer 3.2

準備

ブロックを埋めやすいように大きめサイズの列のテーブルを作る。

create table hoge (hoge_id VARCHAR2(4000) );

インデックスを作る。

create unique index ind_hoge_id on hoge (hoge_id);

ゼロ埋め4000バイトの行を1つ作る。

insert into hoge(hoge_id) values('0000000（省略）00000000');
commit;

この時点のテーブル・インデックスのブロック数などを確認する。

select segment_name, bytes, blocks, extents
from   user_segments
where  segment_name IN ('IND_HOGE_ID', 'HOGE');

特に異変は無し。

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
HOGE	65536	8	1
IND_HOGE_ID	65536	8	1

インデックスレンジスキャンの実行計画、統計を取っておく。

set autotrace on;
select hoge_id from hoge where hoge_id > '0';

これといっておかしなところは無い。

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       4383  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

UPDATEしまくる

とりあえず適当に4000回ほどUPDATEしてみる。where句の無いUPDATEだけど、どうせ1行しかないないし問題無いと思われる。同じ値のままってのもアレなんで、とりあえずプラス1していくUPDATEにしてみた。

begin
  for i in 1..4000 loop
    update hoge set hoge_id = lpad(hoge_id + 1,4000,'0');
    commit;
  end loop;
end;

テーブル・インデックスのブロック数などを確認する。と、インデックスのブロック数が激増している。テーブルのブロック数は変わらないにも関わらず。確かに、インデックスは削除→挿入を繰り返しているだろうことが確認できる。この場合、インデックス末尾の4000バイト以外、より正確にはその行が入っているブロック以外はすべてゴミってことになる。

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
HOGE	65536	8	1
IND_HOGE_ID	12582912	1536	27

領域は無駄であるが、SELECTの実行時間に影響を及ぼさなければ事態はまだマシというものである。

再度、インデックスレンジスキャンの実行計画、統計を取る。

set autotrace on;
select hoge_id from hoge where hoge_id > '0';

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        807  consistent gets
          0  physical reads
          0  redo size
       4383  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

実行計画はコストがちょっと上がってるくらい。あと、バッファキャッシュ消して実行するの忘れたんで、体感の実行時間に差は出なかった。しかし、consistent getsが1から800にもなっている。返される行はどちらにせよ1件しかないので、800ブロックも読み込むのは相当なオーバーヘッドである。

約1500ブロックのうち半分の800にアクセスするのは何故か。

もし、あるリーフブロックのすべての行が削除されると、ブランチブロックに存在する該当リーフブロックを示すブランチ行にも削除表示が挿入される。同じく下位に存在するあるブランチブロックがすべて削除されると、上位のブランチブロックに存在する該当ブロックを示す行に削除表示が挿入される。そのため、ルートブロックを起点として開始点を探す時は不必要な作業が発生しないが、範囲処理を行う時には削除されたブロックもアクセスされることがある。
データベースパフォーマンスアップの教科書基本原理編第2章インデックスの種類と特徴 2.1.2 B-Treeインデックスの操作（Operation） 3）データ削除および更新 p.53 より抜粋

ちょっと引用が長くなってしまったが。この実験のインデックスは、ツリーの半分は削除マークのついたブランチなのでこれはトラバースする必要が無い。よって、残り半分のブロックを範囲処理することになり、このような結果になっているのだと思われる。

実際、ユニークスキャンにするとconsistent getsは激減する。

set autotrace on;
select hoge_id from hoge where hoge_id = lpad(4000,4000,'0');

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       4383  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

インデックスの再生成

てなわけで、こういう場合はインデックスの再生成が必要になる。といっても、容量のムダ使いには目を瞑ってユニークスキャンしかしないのでればやる必要は薄いのだろうけど。運用方針次第な気がする。

alter index ind_hoge_id rebuild;

するとキレイにブロック数が減る。

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
HOGE	65536	8	1
IND_HOGE_ID	65536	8	1

感想とか

下記抜粋の通りなことが確認できました。

（削除、挿入という動作になるのは）インデックス行がソートされて保存されなければならないためにやむを得ず生じる処理である。このような理由があるため、修正が頻繁に発生ない列をインデックスで使用するようお勧めする。
（中略）
インデックスは挿入時だけでなく、削除が更新が発生した場合にも、常に保存領域を多く消費し、これによってツリー構造の深さが増加する。そのため、データ処理（DML）が多く実行されるテーブルは定期的に再生成する必要がある。
データベースパフォーマンスアップの教科書基本原理編第2章インデックスの種類と特徴 2.1.2 B-Treeインデックスの操作（Operation） 3）データ削除および更新 p.53 より抜粋