インデックスのTree Dumpを取得する方法（Oracle12cR2）

Oracle 12c(R2)でインデックスのTree Dumpを取得してみます。

テスト用のテーブルを作成する

まず、テスト用のテーブルとインデックスを作成します。
レコードは1万件挿入しています。

SQL> create table test_treedump (id number, name varchar2(50));                                                          

表が作成されました。

SQL> insert into test_treedump select rownum, 'TEST DATA' || rownum from dual connect by level <=10000;

10000行が作成されました。

SQL>  commit;

コミットが完了しました。

SQL> create index idx_test_treedump01 on test_treedump(id);

索引が作成されました。

インデックスのオブジェクトIDを取得する

インデックスのTree Dumpを取得する際に、オブジェクトIDを指定する必要があるため、先ほど作成したインデックスのオブジェクトIDを調べます。

SQL> select object_id from user_objects where object_name = 'IDX_TEST_TREEDUMP01';

 OBJECT_ID
----------
     66670

このオブジェクトIDをメモしておきます。

インデックスのTree Dumpを取得する

インデックスのTree Dumpは取得するとトレースファイルに出力されます。
出力されたトレースファイルが特定しやすいようにトレースファイルのファイル名を指定しておくと便利です。

次からの操作は、sysユーザで実行します。

SQL> alter session set tracefile_identifier = "TREEDUMP_SQLTRACE";

セッションが変更されました。

上のように実行すると、トレースファイルは以下のように、拡張子の前に指定した"TREEDUMP_SQLTRACE"が付与されます。

XXXX_TREEDUMP_SQLTRACE.trc

それでは先ほど取得したオブジェクトIDを指定して、インデックスのTree Dumpを取得しましょう。

SQL> alter session set events 'immediate trace name treedump level 66670';

セッションが変更されました。

これでインデックスのTree Dumpがトレースファイルに出力されます。

Tree Dumpを確認する

トレースログは以下のファイル名で出力されていたので、これを確認してみます。

XXXX_ora_12064_TREEDUMP_SQLTRACE.trc

以下、トレースファイル中のTree Dump部分の抜粋です。
なお、今回12cR2を用いていますが、古いバージョンですと表示のされ方が若干異なります。

----- begin tree dump
branch: 0x140016b 20971883 (0: nrow: 21, level: 1)
   leaf: 0x140016c 20971884 (-1: row:485.485 avs:824)
   leaf: 0x140016d 20971885 (0: row:479.479 avs:816)
   leaf: 0x140016e 20971886 (1: row:479.479 avs:816)
   leaf: 0x140016f 20971887 (2: row:479.479 avs:816)
   leaf: 0x1400170 20971888 (3: row:479.479 avs:816)
   leaf: 0x1400171 20971889 (4: row:478.478 avs:830)
   leaf: 0x1400172 20971890 (5: row:479.479 avs:816)
   leaf: 0x1400173 20971891 (6: row:479.479 avs:816)
   leaf: 0x1400174 20971892 (7: row:479.479 avs:816)
   leaf: 0x1400175 20971893 (8: row:478.478 avs:830)
   leaf: 0x1400176 20971894 (9: row:479.479 avs:816)
   leaf: 0x1400177 20971895 (10: row:479.479 avs:816)
   leaf: 0x1400179 20971897 (11: row:479.479 avs:816)
   leaf: 0x140017a 20971898 (12: row:479.479 avs:816)
   leaf: 0x140017b 20971899 (13: row:478.478 avs:830)
   leaf: 0x140017c 20971900 (14: row:479.479 avs:816)
   leaf: 0x140017d 20971901 (15: row:479.479 avs:816)
   leaf: 0x140017e 20971902 (16: row:479.479 avs:816)
   leaf: 0x140017f 20971903 (17: row:479.479 avs:816)
   leaf: 0x1402580 20981120 (18: row:478.478 avs:830)
   leaf: 0x1402581 20981121 (19: row:418.418 avs:1731)
----- end tree dump

branch: 0x140016b 20971883 (0: nrow: 21, level: 1)

B*TREEのブランチを表しています。
nrow: 21で、子が21個あることが分かります。
"level: 1"は1階層目のブランチということです。

leaf: 0x140016c 20971884 (-1: row:485.485 avs:824)

"0x140016c 20971884"はアドレスで、左が16進数、右が10進数です。
"-1:"は親からの相対位置で、理由は分かりませんが-1から始まります。ブランチの子は21個でしたので、この値は-1から19までとなります。
"row:485.485"は、リーフは実際に485個使用（左側の数値）されており、削除された分を除くと485個のデータが格納（右側の数値）されていることを示しています。
"avs:824"は空き容量で、リーフに824byteの空き容量があることを示しています。

先ほどは1階層だけでしたが、レコード数を増やすと次のように"level: 2"と2階層に増えます。

branch: 0x140016b 20971883 (0: nrow: 3, level: 2)
   branch: 0x1402cf4 20983028 (-1: nrow: 671, level: 1)
      leaf: 0x140016f 20971887 (-1: row:540.540 avs:0)
      leaf: 0x140016c 20971884 (0: row:533.533 avs:6)
      ～省略～
   branch: 0x1402cf8 20983032 (0: nrow: 670, level: 1)
      leaf: 0x1402cf0 20983024 (-1: row:500.500 avs:1)
      ～省略～
   branch: 0x14033ab 20984747 (1: nrow: 658, level: 1)
      leaf: 0x14033a7 20984743 (-1: row:500.500 avs:1)
      ～省略～

データを削除してみる

400レコード削除して、Tree Dumpがどのように変化するかを確認してみます。

SQL> delete test_treedump where id between 1 and 400;

400行が削除されました。

SQL> commit;

コミットが完了しました。

Tree Dumpを取得すると以下のように出力されています。

----- begin tree dump
branch: 0x140016b 20971883 (0: nrow: 21, level: 1)
   leaf: 0x140016c 20971884 (-1: row:485.85 avs:824)
----- end tree dump

400レコード削除すると、リーフの"row:485.485"が"row:485.85"と、右側の数値が400減っていることが分かります。
右側の数値は削除されたレコードを除いた数ですので、それと一致しています。
ただし、空き容量は824bytesから変わっていません。これは削除されてもまだ再利用対象になっていないということだと思います。

続いて1レコード挿入した場合のTree Dumpを見てみましょう。

SQL> insert into test_treedump values(1, 'TEST DATA1');

1行が作成されました。

SQL> commit;

コミットが完了しました。

1レコード挿入すると、空き容量が824bytesから6707bytesに増えて、データ数も"485.85"から"86.86"に変わりました。

branch: 0x140016b 20971883 (0: nrow: 21, level: 1)
   leaf: 0x140016c 20971884 (-1: row:86.86 avs:6707)

これにより削除されたレコードのインデックスの領域は、再利用されるということが分かります。
同じリーフにデータが格納されるようなデータの持ち方をテーブルがしている場合は、インデックスの領域は再利用されるので、インデックスのリビルドはそれほど必要としないでしょう。
逆に言えば、そうでないケースはインデックスをリビルドした方が良いケースもあるということになります（あまりないかと思いますが）。

インデックスの統計情報取得

ついでなのでインデックスの統計情報取得も試してみます。
インデックスの統計情報を取得するためには、"analyze index"を実行します。
以下は実行例です。

SQL> analyze index idx_test_treedump01 validate structure;

索引が分析されました。

SQL> select height, blocks, lf_rows, lf_blks, del_lf_rows, distinct_keys from index_stats;

なお、テーブルに表ロックをかけるので注意が必要です。

実際に先ほど、1万レコード挿入、400レコード削除、1レコード挿入したときのインデックスの統計情報を以下に示します。

1万レコード挿入直後
    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- -------------
         2         32      10000         21           0         10000

400レコード削除後
    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- -------------
         2         32      10000         21         400         10000
※400レコード削除してもLF_ROWSは10000から変わりません。
　DEL_LF_ROWSが400に増えます。

1レコード挿入後
    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- -------------
         2         32       9601         21           0          9601
※DEL_LF_ROWSは0になり、LF_ROWSは10000-400+1=9601になります。

LF_ROWSとDEL_LF_ROWSがインデックスのTree Dumpの結果と同じになっていることが分かるかと思います。

”validate structure”はオンラインでも実行できるとドキュメントに記載されていました。これなら表ロックがかからないかと思いましたが、実際に実行すると結果が取得できませんでした。

ONLINE | OFFLINE ONLINEを指定すると、Oracle DatabaseがオブジェクトのDML操作中に検証を実行できるようになります。データベースは、並行して操作が行える程度に、実行する検証の量を減らします。

SQL Language Reference Release 12.2 - 12.10 ANALYZE

同ドキュメントには以下の注意事項が記載されているので、あくまでインデックスの構造を検証するだけで、index_statsテーブルには統計情報は格納されないということですね。

注意:
OFFLINE指定でオブジェクトの構造を検証する場合と同様に、ONLINE指定でオブジェクトの構造を検証する場合、Oracle Databaseは統計情報を収集しません。

”validate structure”はあくまでもインデックスの構造の検証目的で使用するものであり、統計情報を取得するのであればDBMS_STATSを使用し、USER_INDEXESテーブルで確認しなさいということでしょう（きっと）。
しかしながら、開発中は”validate structure”を使用するのが楽ですし、これからもANALYZEします。

参考