Re: Strange Behaviour on Index Space Usage, help please

"Michel Cadot" <micadot_at_netcourrier.com> wrote:
>Answer embedded.
>
>--
>Have a nice day
>Michel
>
>
>Reza <reza_at_digital-dispatch.co.uk> a écrit dans le message :
>963484572.10474.0.nnrp-04.d4f07aaa_at_news.demon.co.uk...
>> As far as I know at least in Oracle 7.3 if you delete a
 record, the
>> corresponding index gets deleted but the space of this index
 won't be used
>> again until you rebuild the index, Am I correct?
>
>Deleted index entries are reused if and only if you insert
>the same key as the previous deleted one.
>
>> I have a 7.3 database which creates 30MB of index on a
 certain table's
>> entries. When I delete nearly same number of old records from
 the table,
>> then index space usage does not grow any more and it looks
 that the index
>> space in the tablespace is reused. Is this true?
>
>Object space is not reused for other objects unless you
 deallocate
>this space (statement alter table/index ... deallocate unused)
 or
>delete the object.
>
>> Does this have anything to do with high water mark on the
 tablespace used
>> space, since no matter how more record I delete from the
 table, no more free
>> space is released to its index's tablespace.
>
>AFAIK there is no tablespace HWM. There is a table HWM which
 gives
>the limit between the blocks used at least once and the blocks
 of
>the table never used. It gives the number of blocks you can
 deallocate
>(all of them above the HWM).
>
>> Can anyone please shed any light on this matter please.
>>

1) Once allocated to an object space is not released for use by another object unless you issue a deallocate command and this works only on space above the hwm on a table. Or the object is rebuilt.

2) Index blocks where ALL rows in the block are deleted from them are placed back on the free list so they are reused. If data keys are inserted together and deleted together in groups then new rows have a tendency to go into empty blocks and the deletes create empty blocks.

Also space created by a delete is not available for reuse until after a commit is issued so if you delete and insert the same rows in one transaction the space need is double that of using two separate transactions.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 13 2000 - 00:00:00 CDT