続・INDEXに関する検証 その２
＜続・INDEXに関する検証 その２＞ ペンネーム モンキーターン
— 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に関する検証 その２～
by モンキーターン