Fragmentation

Jonathan Lewis hat in den letzten Wochen wieder allerlei interessante Blog-Einträge veröffentlicht, darunter auch eine weit gefasste Begriffserklärung für den Terminus Fragmentation. In dem Teil, der den Begriff im Kontext von Indizes erläutert, findet sich eine sehr erinnerungswürdige Kurzzusammenfassung zum Thema der Wiederverwendung von Speicherplatz in Tabellen und Indizes nach Satzlöschungen:

we have to remember that there is a difference between index deletions and table deletions that makes the subsequent behaviour different. When a transaction deletes a row from a table it can reduce the row to a stub of just a few bytes before the commit takes place and immediately make use of the space this reduction frees up in the table block; when a transaction deletes a row from an index it has to leave the entire index entry in place and flag it as deleted – it cannot immediately reuse the space, it has to wait until after the commit.

Eine Operation, die in einer Transaktion Sätze löscht und einfügt, sollte einen Index demnach deutlich wachsen lassen; dazu ein kleiner Test, in dem ich eine Tabelle mit Index anlege, alle Sätze der Tabelle lösche und anschließend identische Sätze einfüge:

--  Test1: ohne commit nach Satzlöschung
SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL>  exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                    estimate_percent=>dbms_stats.auto_sample_size)

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              2197

Ohne Commit wächst der Index also tatsächlich auf die doppelte Größe.

--  Test2: mit commit nach Satzlöschung
 SQL> drop table test;

Tabelle wurde gelöscht.

SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

 SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL> exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                   estimate_percent=>dbms_stats.auto_sample_size)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099 

In diesem Fall wächst der Index nicht, da der Platz nach dem Commit freigegeben wurde. Der Herr Lewis hat also recht, was mich jetzt aber auch nicht besonders überrascht...

Für einen unique index würde das hier verwendete Beispiel übrigens eine Wiederverwendung des Speicherplatzes zeigen, da dies eine Besonderheit dieses Index-Typs ist. Bei Richard Foote findet man eine umfangreichere Erläuterung dieses Verhaltens.