小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】indexの貼られたテーブルを全件deleteしてみた

oracle
はじめに

インデックスの貼られたテーブルを全件deleteしたときのインデックスツリーの変化を見ていきます。

環境
  • Oracle Database 12c EE 12.1.0.1.0
テスト用のテーブル
CREATE TABLE T_GAKUSEI
  (
    gakusekiNo NUMBER,--学籍番号
    classNo    NUMBER,--クラスid
    name       VARCHAR2(200),--指名
    furigana   VARCHAR2(200)--ふりがな
  );

学籍番号とクラスidに複合インデックスを作成します。

CREATE INDEX idx01_T_GAKUSEI ON T_GAKUSEI(gakusekiNo, classNo);

学生が1000人いるとして、データを挿入していきます。クラスidは1〜5組をランダムで振り分けています。

INSERT
  /*+ APPEND */
INTO T_GAKUSEI nologging
  (
    gakusekiNo,
    classNo,
    name,
    furigana
  )
  (SELECT 10*rownum,
      ROUND(dbms_random.value(1, 5), 0),
      concat('学生　太郎', TO_MULTI_BYTE(10*rownum)),
      concat('がくせい　たろう', TO_MULTI_BYTE(10*rownum))
    FROM
      (SELECT 0 FROM all_catalog WHERE rownum <= 100
      ),
      (SELECT 0 FROM all_catalog WHERE rownum <= 10
      )
  );
COMMIT;
インデックスの情報を取得してみる
ANALYZE INDEX idx01_T_GAKUSEI VALIDATE STRUCTURE;
SELECT NAME AS インデックス名,
  BLOCKS    AS ブロック数,
  LF_ROWS   AS リーフ行数,
  LF_BLKS   AS リーフブロック数,
  BR_BLKS   AS ブランチのブロック数
FROM INDEX_STATS;

f:id:replication:20150919151745p:plain

tree dumpは、以下のとおりです。枝が１個、葉が３つのインデックスツリーです。

----- begin tree dump
branch: 0x24000b3 37748915 (0: nrow: 3, level: 1)
   leaf: 0x24000b5 37748917 (-1: nrow: 450 rrow: 450)
   leaf: 0x24000b6 37748918 (0: nrow: 444 rrow: 444)
   leaf: 0x24000b7 37748919 (1: nrow: 106 rrow: 106)
----- end tree dump
全件削除してみる
DELETE FROM T_GAKUSEI;
COMMIT;

全件レコードを削除した後でも、インデックス情報に変化がありません。
f:id:replication:20150919152242p:plain
tree dumpを確認すると、rrowがいずれも0になっていることからレコードが格納されていない空リーフになっています。

----- begin tree dump
branch: 0x24000b3 37748915 (0: nrow: 3, level: 1)
   leaf: 0x24000b5 37748917 (-1: nrow: 450 rrow: 0)
   leaf: 0x24000b6 37748918 (0: nrow: 444 rrow: 0)
   leaf: 0x24000b7 37748919 (1: nrow: 106 rrow: 0)
----- end tree dump

削除した行数、削除率を調べてみると、削除行数が1000行となり、削除率は100%になっていました。

ANALYZE INDEX idx01_T_GAKUSEI VALIDATE STRUCTURE;
SELECT LF_ROWS AS "リーフ行の数",
  LF_BLKS      AS リーフブロック数,
  DEL_LF_ROWS  AS "削除リーフ行の数",
  TO_CHAR(DEL_LF_ROWS/LF_ROWS * 100,'999.9')
  || '%' AS "削除リーフ行の割合"
FROM INDEX_STATS;

f:id:replication:20150919152431p:plain

インデックスの再構築

レコードがない状態にもかかわらずインデックスには空のリーフが存在する状態を解消するには、インデックスの再構築を行います。

ALTER INDEX idx01_T_GAKUSEI REBUILD ONLINE;

ALTER INDEX実行後、再度アナライズすると、
f:id:replication:20150920000740p:plain
リーフ行数がゼロになりました。
tree dumpもブランチが消え、リーフも0行になりました。

----- begin tree dump
leaf: 0x24000f3 37748979 (0: nrow: 0 rrow: 0)
----- end tree dump
まとめ

インデックスが貼られているレコードを削除すると、空のリーフが残ってしまいます。
定期的にインデックスのリビルドをしなさいと言われるのは、この理由によるもの。

関連記事