[索引相关] 索引空间的重用

-- 一旦插入了可以重用空间的行，索引空间立即重用
-- 索引块为空时，索引的freelist上可以看到，被回收，可以重用

go@DEMO> create table t (x int,constraint t_pk primary key(x));

Table created.

go@DEMO> insert into t values (1);

1 row created.

go@DEMO> insert into t values (2);

1 row created.

go@DEMO> insert into t values (9999999999);

1 row created.

go@DEMO> commit;

Commit complete.

go@DEMO> select * from t;
         X
----------
         1
         2
9999999999

3 rows selected.

go@DEMO> analyze index t_pk validate structure;

Index analyzed.

go@DEMO> select lf_blks,br_blks,btree_space from index_stats;
   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         1          0        8000

1 row selected.

go@DEMO>
begin
for i in 2..999999
    loop
        delete from t where x=i;
        commit;
        insert into t values (i+1);
        commit;
    end loop;
end;
/

PL/SQL procedure successfully completed.

go@DEMO> select lf_blks,br_blks,btree_space from index_stats;
   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         1          0        8000

1 row selected.