続・INDEXに関する検証 その2

<続・INDEXに関する検証 その2> ペンネーム モンキーターン

— UPDATEとINDEXの関係 —

前回のメルマガで INDEXキーを同一の値で UPDATEすると INDEXにどのような
変更が行われたか、わかっていただけたと思う。
今回は、INDEXキーが異なる値で更新された場合の UPDATEと INDEXの関係につ
いて、INDEXを中心に検証を交えながら説明していこう。

— どっぷり検証生活 START! —

今回も前回使用した検証用テーブルと INDEXを使用する。

<<<検証に用いたSQL文>>>

SQL> UPDATE EMP_TEST SET EMPNO = 1111 WHERE EMPNO = 7369 ;
1行が更新されました。

SQL> COMMIT ;
コミットが完了しました。

SQL> ALTER SYSTEM CHECKPOINT ;(alter system権限が必要)
システムが変更されました。

なぜ、CHECKPOINTを発生させたのかは前回のメルマガを参照

<<<BLOCK_DUMPの取得>>>

取得方法は前回のメルマガを参照

<<結果>>

異なる値(7369⇒1111)で UPDATEする前のINDEX_BLOCK_DUMPの結果

row#0[1879] flag: ----, lock: 0
col 0; len 3; (3):  c2 4a 46   --------------→(1)(EMPNO=7369)
col 1; len 6; (6):  02 00 00 03 00 00
row#1[1866] flag: ----, lock: 0
col 0; len 3; (3):  c2 4b 64
col 1; len 6; (6):  02 00 00 03 00 01
row#2[1853] flag: ----, lock: 0
col 0; len 3; (3):  c2 4c 16
col 1; len 6; (6):  02 00 00 03 00 02

・
・
・

row#12[1724] flag: ----, lock: 0
col 0; len 3; (3):  c2 50 03
col 1; len 6; (6):  02 00 00 03 00 0c
row#13[1711] flag: ----, lock: 0 ------------→(2)(row# 13)
col 0; len 3; (3):  c2 50 23
col 1; len 6; (6):  02 00 00 03 00 0d
----- end of leaf block dump -----

異なる値(7369⇒1111)で UPDATEした後のINDEX_BLOCK_DUMPの結果

row#0[1698] flag: ----, lock: 2
col 0; len 3; (3):  c2 0c 0c    -------------→(3)(EMPNO=1111)
col 1; len 6; (6):  02 00 00 03 00 00
row#1[1879] flag: --D-, lock: 2 -------------→(4)(flag=D)
col 0; len 3; (3):  c2 4a 46    -------------→(5)(EMPNO=7369)
col 1; len 6; (6):  02 00 00 03 00 00
row#2[1866] flag: ----, lock: 0
col 0; len 3; (3):  c2 4b 64
col 1; len 6; (6):  02 00 00 03 00 01

・
・
・

row#13[1724] flag: ----, lock: 0
col 0; len 3; (3):  c2 50 03
col 1; len 6; (6):  02 00 00 03 00 0c
row#14[1711] flag: ----, lock: 0 ------------→(6)(rows# 14)
col 0; len 3; (3):  c2 50 23
col 1; len 6; (6):  02 00 00 03 00 0d
----- end of leaf block dump -----

ここで注目していただきたいのは、row#と flagである。

row#
(2)UPDATE前 row#0 ~ row#13(14レコード存在していることを意味している)
(6)UPDATE後 row#0 ~ row#14(15レコード存在していることを意味している)

UPDATE後、row#が増えているのが確認できる。

では、UPDATE前とUPDATE後に何が増えたのかを見ていこう。
EMPNO=7369(1)を EMPNO=1111(3)に UPDATEを行った。しかし、UPDATE
したはずの EMPNO=7369(5)が flagに D(4)を立てて残っているのが確認
できる。

また、INDEXは INDEXキーを SORTした状態でデータを保持しているが、実デー
タが格納されているテーブルには存在しないデータ(削除されたため)も、IN
DEX内では flagに Dを立てた上で、SORTの対象となっていることが確認できた。

テーブル中のデータを削除したとしても、INDEXの構造は変わることなく、実際
には Leaf_BLOCK中にデータが flag = D として管理され残ったままになるので
ある。このデータ(flag = Dと管理されたデータ)も、レンジ検索時には検索
の対象となってしまうため、パフォーマンスに影響を及ぼしてしまうのである。
10万件のデータ中 9万件を削除したような場合、空のLeaf_BLOCKに I/Oが発生
していたかのように見えていたが、実は Leaf_BLOCK中にデータが残っていたの
である。

なぜ、このような処理を施すのであろうか?
それは、INDEXの構造上の問題である。削除されたからといって、そのLeaf_BLOCK
を切り離すのではなく、再度同じ値が入ってきたときのために使う領域
(Place Holder)として保持していると考えられる。実際に、一度削除した値
を再度入力(INDEXキーが同じ値)した場合、INDEXの構造上の問題で大きな組
み替えが起きていない限り、必ず同じ Leaf_BLOCKに入ることは検証済みである。

次回は、INDEXの情報元である、BLOCK_DUMP、TREEDUMP、INDEX_STATSビューの
データの整合性について検証してみる。

<<<余談>>>

INDEXをより活用する方法としてオプティマイザ・ヒントがある。INDEXは作っ
ただけでは何の意味もない。使わせてなんぼである。SQL文をコーディングする
ときに「オプティマイザに自分の意志を伝える」という機能を使わない手はな
い。

宝の持ち腐れならぬ INDEXの持ち腐れにならないように!

サザン凱旋 茅ヶ崎にて

~続・INDEXに関する検証 その2~
by モンキーターン