インデックスのshrinkとrebuildの違いを検証

ANALYZE INDEX VALIDATE STRUCTUREでインデックス情報を取得し違いをさくっと検証した。
注意：ANALYZE INDEX VALIDATE STRUCTURE 実行中のDML処理は、ANALYZEによるロックが開放されるまで待機してしまう。

現在のインデックスの状態

SQL> analyze index TEST_IDX  validate structure;
索引が分析されました。

SQL> select NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS from INDEX_STATS;

NAME              HEIGHT    LF_ROWS DEL_LF_ROWS
------------- ---------- ---------- -----------
TEST_IDX               4   34744558      115444

カラム	説明	値
HEIGHT	Bツリーの高さ	4
LF_ROWS	リーフ行の数(索引内の値)	34744558
DEL_LF_ROWS	索引内の削除されたリーフ行の数	115444

shrinkの場合

SQL> alter index TEST_IDX shrink space;

索引が変更されました。

SQL> select NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS from INDEX_STATS;

NAME              HEIGHT    LF_ROWS DEL_LF_ROWS
------------- ---------- ---------- -----------
TEST_IDX               4   34629374         260

rebuildの場合

SQL> alter index TEST_IDX rebuild;

索引が変更されました。

SQL> select NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS from INDEX_STATS;

NAME              HEIGHT    LF_ROWS DEL_LF_ROWS
------------- ---------- ---------- -----------
TEST_IDX               3   34629374           0

まとめ

カラム	初期	shrink	rebuid
HEIGHT	4	4	3
LF_ROWS	34744558	34629374	34629374
DEL_LF_ROWS	115444	260	0

shrinkの場合はDEL_LF_ROWS（論理削除済みの断片化されたインデックスデータ）をある程度削除する。
rebuildの場合はHEIGHT（B-treeの深さ）を改善しとDEL_LF_ROWSを完全削除する。インデックスが再作成される。