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

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

前回までの検証で、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に関する検証 その3~
by モンキーターン