Index Space Reuse


Still plenty of practitioners out there that think that indexes never reuse space when entries are deleted. Well its true that if your inserts and deletes are "random" then you might see some growth, but even under these conditions, indexes have a habit of finding an equilibrium point. Here's an example


SQL> drop table t1;

Table dropped.

SQL>
SQL> create table T1 as
  2  select rownum x from all_objects
  3  where rownum < 30000;

Table created.

SQL>
SQL> create index T1X on T1 ( x );

Index created.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
        66          1           0         88

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
        82          1          50         71

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       104          1          53         57

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       116          1          77         51

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       119          1          68         50

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       123          1          92         48

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       125          1          89         47

SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       127          1          94         47

SQL>
SQL> declare
  2   l number; r number := 0; c number;
  3  begin
  4  for i in 1 .. 100000 loop
  5   l := trunc(dbms_random.value(1,30000));
  6   delete from t1 where x = l;
  7   if sql%found then
  8     c := sql%rowcount;
  9     r := r + c;
 10     commit;
 11     for j in 1 .. c loop
 12       l := trunc(dbms_random.value(1,30000));
 13       insert into t1 values (l);
 14     end loop;
 15   end if;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> analyze index T1X validate structure;

Index analyzed.

SQL>
SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

   LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ----------- ----------
       127          1          92         47

SQL>