続・INDEXに関する検証 その４
＜続・INDEXに関する検証 その４＞ ペンネーム モンキーターン
前回は 10万件のデータの内、9万件削除した際のINDEX・キーが、Leaf_BLOCK
中で flag = Dと管理されている様子を見てきた。
今回は flag = Dで管理されたデータが開放される様子と引き続き INDEX情報
が、それぞれどのように関連しているかを中心に検証を交えながら説明してい
こう。
— どっぷり検証生活 CONTINUE！ —
flag = Dで管理（INDEX・キーが削除されると、そのキー自体は削除されず、
flag = Dとして管理される）されている INDEX・キーが開放されるタイミング
は、flag = Dで管理されている INDEX・キーが存在する Leaf_BLOCKに PHYSI
CAL READまたは PHYSICAL WRITEが発生したときであることが、今回の検証で
明らかになった。
前回 10万件の検証用テーブルで9万件の削除処理を行ったことで、INDEX・キー
が flag = Dで管理されている状態にある。このテーブルのINDEX（Leaf_BLOCK）
に PHYSICAL READを発生させ、flag = Dで管理されている INDEX・キーが開放
される様子を見ていこう。
＜＜＜検証に用いたSQL文＞＞＞
SQL> SELECT * FROM EMP_TEST WHERE EMPNO > 0 ;
WHERE句で EMPNO > 0 とした目的の 1つは INDEXを使わせるため、二つ目は
EMP_TEST表の EMPNOはすべて 0以上である。よって、すべての Leaf_BLOCK
を SCANさせるためである。言い換えると、すべての Leaf_BLOCKに PHYSIC
AL READを発生させるためである。
＜＜＜INDEX情報の取得方法＞＞＞
前回のメルマガを参照
＜＜＜結果＞＞＞
＜＜SELECT直後のINDEX情報＜TREEDUMP＞＜BLOCK_DUMP＞＜INDEX_STATS＞ ＞＞
＜TREEDUMP＞
----- begin tree dump branch: 0x2400003 37748739 (0: nrow: 7, level: 2) branch: 0x24000aa 37748906 (-1: nrow: 165, level: 1) leaf: 0x2400004 37748740 (-1: nrow: 0 rrow: 0)------→（1） leaf: 0x2400005 37748741 (0: nrow: 0 rrow: 0) leaf: 0x2400006 37748742 (1: nrow: 0 rrow: 0) ・ 中略 ・ leaf: 0x240036a 37749610 (67: nrow: 0 rrow: 0) leaf: 0x240036b 37749611 (68: nrow: 0 rrow: 0) leaf: 0x240036c 37749612 (69: nrow: 46 rrow: 46)----→（2） leaf: 0x240036d 37749613 (70: nrow: 103 rrow: 103) leaf: 0x240036e 37749614 (71: nrow: 103 rrow: 103) ・ 中略 ・ leaf: 0x24003cd 37749709 (7: nrow: 103 rrow: 103) leaf: 0x24003ce 37749710 (8: nrow: 66 rrow: 66) ----- end tree dump
ここで注目していただきたいのは nrowsと rrowsである。
前回で述べたように
nrowsは、flag = Dのデータ数＋有効データ数
rrowsは、有効データ数
を示している。（flag = Dではないキーを「有効」という言葉で表現している）
＜TREEDUMP＞（2）の部分に注目していただきたい。今回の結果は nrows ＝ rrows
になっている（前回の結果では、nrows ＝ rrowsではなかった（下記参照））。
これは、Leaf_BLOCK内に存在していた flag = Dで管理されていたINDEX・キー（57行）
が開放されたことを意味している。
前回の結果 leaf: 0x240036c 37749612 (69: nrow: 103 rrow: 46)
今回の結果 leaf: 0x240036c 37749612 (69: nrow: 46 rrow: 46)
＜BLOCK_DUMP＞
この BLOCK_DUMPは＜TREEDUMP＞の（1）で示したLeaf_BLOCK_DUMPである。
Leaf block dump =============== header address 93596764=0x5942c5c kdxcolev 0 kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 0 kdxcofbo 36=0x24 kdxcofeo 1892=0x764 kdxcoavs 1856 kdxlespl 0 kdxlende 0 kdxlenxt 37748741=0x2400005 kdxleprv 0=0x0 kdxledsz 0 kdxlecol 0 kdxlebksz 1892 ----- end of leaf block dump -----
結果を見ていただいてお分かりの通り、Leaf_BLOCK内にINDEX・キーが存在して
いない。
ちなみに、前回の結果は以下の通りである。
Leaf block dump =============== header address 93596764=0x5942c5c kdxcolev 0 kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 116 kdxcofbo 268=0x10c kdxcofeo 484=0x1e4 kdxcoavs 216 kdxlespl 0 kdxlende 116 kdxlenxt 37748741=0x2400005 kdxleprv 0=0x0 kdxledsz 0 kdxlecol 0 kdxlebksz 1892 row#0[1880] flag: --D-, lock: 2 col 0; len 2; (2): c1 02 col 1; len 6; (6): 02 00 00 03 00 00 ・ ・ ・ ----- end of leaf block dump -----
この Leaf_BLOCKは DELETE直後（前回のメルマガを参照）には、すべてのINDEX
・キーが flag = Dで管理されていたが、今回のSELECT文で、すべてのキーが開
放されているのが確認できる。
＜INDEX_STATS＞
SQL> analyze index emp_test_index validate structure ; 索引が分析されました。 SQL> select name , lf_rows , del_lf_rows , br_rows , blocks from index_stats ; NAME LF_ROWS DEL_LF_ROWS BR_ROWS BLOCKS --------------- --------- ----------- --------- -------- EMP_TEST_INDEX 10000 0 964 1430
ここで注目していただきたいのは、DEL_LF_ROWSとLF_ROWSである。
前回で述べたように
DEL_LF_ROWSは、 flag = Dのデータ数
LF_ROWSは、 flag = Dのデータ数＋有効データ数を表している。
今回の結果、DEL_LF_ROWSが 0に、LF_ROWSは 10000になっている。
結果より flag = Dで管理されていた INDEX・キーが開放されていることが確
認できる。
ちなみに、前回の結果は以下の通りである。
NAME LF_ROWS DEL_LF_ROWS BR_ROWS BLOCKS --------------- --------- ----------- --------- -------- EMP_TEST_INDEX 100000 90000 964 1430
＜総評＞
以上の結果より flag = Dで管理された INDEX・キーが開放される動きとINDEX
の主な情報元である ＜TREEDUMP＞＜BLOCK_DUMP＞＜INDEX_STATS＞のデータが
どのように関連しているか理解していただけたと思う。
INDEXを保守するための情報で一番重要なのは、REBUILDのタイミングを判断す
る情報である。INDEX_STATSビューからの情報で REBUILDのタイミングを判断
している方をよく見かけるが、ここには大きな落とし穴がある。
上記の結果（INDEX_STATS）をご覧の通り、flag = Dで管理されていた INDEX・
キーが開放された後の結果を見ていただくと、DEL_LF_ROWS = 10000が DEL_LF
_ROWS = 0にリセットされてしまっている。
これでは、INDEXの歪みを判断することができない。
INDEXの保守タイミング（REBUILDのタイミング）を知るには、TREEDUMPを取
得し、DBA自らが判断する必要があるのではなかろうか。
秋立つ 茅ヶ崎にて
～続・INDEXに関する検証 その４～
by モンキーターン