db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Behaviour of SYSCS_COMPRESS_TABLE
Date Wed, 01 Jun 2005 17:21:42 GMT
Derby will also reclaim space automatically in btree indexes for this
case as long as all the old rows on a given leaf are deleted and
the background process gets a chance to get a table level lock
at some point - the current algorithm requires a table level lock
to do the index management to delete an entire leaf from the tree.

If you only deleted every other key in this scenario then we would
not reclaim space.

In the case of a even distribution of keys and inserts, then we
try automatic space reclamation any time an insert is going to
cause a split and the destination page has any deleted row.

The hope is that most applications need never call the compress
table functions, and I hope in the future to implement some more
automatic space reclamation techniques based on the work I have
been putting in this release to reclaim space in place.

Currently the application we don't handle well is one with does
a lot of deletes and never does another insert.  In that case
the space sits there ready to use for future inserts, but it is
not returned to the OS automatically.

Øystein Grøvlen wrote:
>>>>>>"MM" == Mike Matrigali <mikem_app@sbcglobal.net> writes:
>     MM> Note that cloudscape automatically reuses space from deleted rows when
>     MM> new rows are inserted into the table.  The main problem
>     MM> SYSCS_COMPRESS_TABLE is solving is if there are a number of deletes
>     MM> which will not be followed by a number of inserts.  The reuse of space
>     MM> is not as efficient as the compress table at it squeezes every last bit
>     MM> of free space out, and returns that space to the OS.
> Is this also true for B-tree indexes?  I would imagine that if you
> have a index on a monotocally increasing key (e.g., a timestamp) and
> where you regularly delete old records, there may be a lot of empty
> B-tree pages that will never be possible to reuse.

View raw message