Re: Strange Behaviour on Index Space Usage, help please

Mark D Powell wrote:
>
> "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

Just to add some more...

In V7, the space in an totally free block was only available after a commit, thus

delete from blah
insert into blah select ...

would not reuse the space freed by the delete, whereas

delete from blah
commit
insert into blah select ...

would be able to...

I haven't confirmed for V8 but I would suspect the same.

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse