Re: Indexes space use

The standard index structure used to support most Oracle indexes is based on btrieve format logic. When a table row is deleted the corresponding index entry is 'removed'. Since a index entry is based on a key value the index entries must be stored in key order ( we will ignore use of pointers within index for this discussion). The space held by a deleted key can not be used by a different key value except in a limited fashion.

If the same key value is re-inserted if can reuse the space if once had, and if the key value being inserted falls between existing keys that were stored in the same index block as the deleted key and both this value and the deleted key value fall between the same existing end keys then Oracle might be able to reuse this space. In simple terms you have keys 1, 2, 4, and 5 in a block. Key 4 is deleted. Key 3 may well be able to use the space freed by key 4 since by btrieve rules it must be sequenced into this block and falls between existing entries 2 and 5.

If all the keys in a block are deleted then that block goes back onto the free space list and the entire block becomes available for re-use, but even one remaining key value prevents the block from being reused.

Experiment:
Build table with say, 1000 rows with key values 1 - 1000. Build unique index on key.
Examine size. Size the index so the first 1000 rows fill the allocated space as close as possible.

Insert key 1001 - 2000. Examine size of index. ( Idx about double)

Start over. To table delete key 1 - 1000, no commit, insert 1001 - 2000. Check size. (idx should be about double in size)

Start over. To table delete key 1 - 1000, commit, insert 1001 - 2000. Check. (Index did not grow)

Try rerunning deleting every other key and re-inserting the keys with and without commits between the delete and insert operation. Post results if you have time.

ThierryHuge <ThierryHuge_at_france-mail.com> wrote in article <35DD3ADE.24FDC4C5_at_france-mail.com>...
> I have a table T which have an index I
> Users run a lot of insert/delete operations on T (then on I)
> Each time the key is different. It seems the blocks allocated for I are
> not reused.
> Table T has always a few number of lines but I have a lot of extents
> allocated for I.
>
> Could someone explain how space is allocated for the index when a lot of
> insert/delete are run onto the corresponding table ?
>
>
Received on Fri Aug 21 1998 - 08:40:22 CDT