続・INDEXに関する検証 その３
＜続・INDEXに関する検証 その３＞ ペンネーム モンキーターン
前回までの検証で、INDEXは削除されたデータを、flagを用いて管理している
ことが分かった。
では、いつまで管理し続けるのであろうか？
結論から述べると、flag = Dで管理されているキーが存在する Leaf_BLOCKに、
PHYSICAL READまたは PHYSICAL WRITEが発生すると、flag = Dで管理されてい
る INDEXキーは開放されるという結果を得た。
今回は、INDEXに対して PHYSICAL READまたは PHYSICAL WRITEを発生させずに、
INDEXの主な情報元である、TREEDUMP、BLOCK_DUMP、INDEX_STATSビュー それぞ
れの内容を確認してみよう。上記 3つの INDEX情報が、それぞれどのように関
連しているかを中心に検証を交えながら説明してこう。
次回、INDEXに対して PHYSICAL READまたは PHYSICAL WRITEを発生させて、同
様の検証を行ない、今回との違いを明らかにする。
— どっぷり検証生活 START！ —
＜＜＜検証用テーブルとINDEXの作成＞＞＞
1．ユーザー SCOTTで EMP_TESTと言うデータ件数が10万件のテーブルを作成
（中身は EMP表と同じ）
2．表の項目 EMPNOに対して、EMP_TEST_INDEXと言う INDEXを作成
（INDEXは別の表領域に作成）
＜＜＜検証に用いたSQL文＞＞＞
SQL> DELETE FROM EMP_TEST WHERE ROWNUM < 90001 ; 90000行が削除されました。 SQL> COMMIT ; コミットが完了しました。
＜＜＜INDEX情報の取得方法＞＞＞
TREEDUMP、BLOCK_DUMP、INDEX_STATSの情報を取得する。
これらの情報の取得方法を以下に示す。
TREEDUMP ⇒（以下、クリックで大きく表示。）
BLOCK_DUMP ⇒（以下、クリックで大きく表示。）
INDEX_STATS ⇒（以下、クリックで大きく表示。）
＜＜＜結果＞＞＞
＜＜DELETE直後の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: 116 rrow: 0)------→（1） leaf: 0x2400005 37748741 (0: nrow: 110 rrow: 0) leaf: 0x2400006 37748742 (1: nrow: 110 rrow: 0) ・ 中略 ・ leaf: 0x240036a 37749610 (67: nrow: 103 rrow: 0) leaf: 0x240036b 37749611 (68: nrow: 103 rrow: 0) leaf: 0x240036c 37749612 (69: nrow: 103 rrow: 46) 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は、削除されたことによって、flag = Dで管理されることになったキー
の数と、flag = Dではないキーの数を合計したものである。つまり、削除の
有無に関わらず、その Leaf_BLOCK中に存在する INDEXキーの数を表したもの
である。（以下 flag = Dではないキーを「有効」という言葉で表わす。）
rrowsは、その Leaf_BLOCK中に存在する flag = Dではない有効な INDEXキー
の数のみを表したものである。
簡単に表すと
nrowsは、flag = Dのデータ数＋有効データ数
rrowsは、有効データ数
である。
＜BLOCK_DUMP＞
以下の BLOCK_DUMPは、＜TREEDUMP＞の（1）で示しているLeaf_BLOCKのDUMPである。
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 row#1[1868] flag: --D-, lock: 2 col 0; len 2; (2): c1 03 col 1; len 6; (6): 02 00 00 03 00 01 ・ ・ ・ row#114[497] flag: --D-, lock: 2 col 0; len 3; (3): c2 02 10 col 1; len 6; (6): 02 00 00 05 00 1e row#115[484] flag: --D-, lock: 2 col 0; len 3; (3): c2 02 11 col 1; len 6; (6): 02 00 00 05 00 1f ----- end of leaf block dump -----
ブロック内のINDEXキーがすべて削除されたことによって、すべてのキーが f
lag = Dで管理されている様子が伺える。また、このBLOCK_DUMPの flag = Dの
数は、116（row#0～row#115）で有効データ数は 0である。これは、TREEDUMP
の nrows = 116 と rrows = 0の数と一致する。
＜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 100000 90000 964 1430
10万件存在する INDEXキーのうち、9万件が削除されたことを意味している。
削除されたことにより、有効な INDEXキーは1万件しか存在しないが、実際に
は、削除されたキーを flag = Dで管理するだけで、インデックスの構造は変
わらず、削除前の10万件がそのまま残っている。
DEL_LF_ROWSは、flag = Dのデータ数
LF_ROWSは、flag = Dのデータ数＋有効データ数を表している。
flag = Dで管理されていない、有効な INDEXキーの数を求めるには、LF_ROWS
から DEL_LF_ROWSを差し引いてあげればよい。
「 LF_ROWS = 有効な INDEXキーの数 」と勘違いされがちなので、
注意が必要である。
夏の暮れ 茅ヶ崎にて
～続・INDEXに関する検証 その３～
by モンキーターン