Database Reference
In-Depth Information
EODA@ORA12CR1> select lf_blks, br_blks, btree_space from index_stats;
LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- -----------
1 0 7996
So, according to the myth, if I delete from
T
where
X=2
, that space will never be reused unless I reinsert the
number 2. Currently, this index is using one leaf block of space. If the index key entries are never reused upon deletion,
and I keep inserting and deleting and never reuse a value, this index should grow like crazy. Let's see:
EODA@ORA12CR1> begin
2 for i in 2 .. 999999
3 loop
4 delete from t where x = i;
5 commit;
6 insert into t values (i+1);
7 commit;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> analyze index t_pk validate structure;
Index analyzed.
EODA@ORA12CR1> select lf_blks, br_blks, btree_space from index_stats;
LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- -----------
1 0 7996
This shows the space in the index was reused. As with most myths, however, there is a nugget of truth in there.
The truth is that the space used by that initial number 2 would remain on that index block forever. The index will not
coalesce
itself. This means if I load a table with values 1 to 500,000 and then delete every other row (all of the even
numbers), there will be 250,000 holes in the index on that column. Only if I reinsert data that will fit onto a block
where there is a hole will the space be reused. Oracle will make no attempt to shrink or compact the index. This can
be done via an
ALTER INDEX
REBUILD
or
COALESCE
command. On the other hand, if I load a table with values 1 to
500,000 and then delete from the table every row where the value was 250,000 or less, I would find the blocks that were
cleaned out of the index were put back onto the
FREELIST
for the index. This space can be totally reused.
If you recall, this was the second myth: index space is never
reclaimed
. It states that once an index block is used, it will
be stuck in that place in the index structure forever and will only be reused if you insert data that would go into that place
in the index anyway. We can show that this is false as well. First, we need to build a table with about 500,000 rows in it.
For that, we'll use the
big_table
script found in the Setting Up Your Environment section in the front of this topic. After
we have that table with its corresponding primary key index, we'll measure how many leaf blocks are in the index and
how many blocks are on the
FREELIST
for the index. Also, with an index, a block will only be on the
FREELIST
if the block
is entirely empty, unlike a table. So any blocks we see on the
FREELIST
are completely empty and available for reuse:
EODA@ORA12CR1> select count(*) from big_table;
COUNT(*)
----------
500000
