db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From The Wogster <wogste...@yahoo.ca>
Subject Re: Behaviour of SYSCS_COMPRESS_TABLE
Date Wed, 01 Jun 2005 18:30:13 GMT
Mike Matrigali wrote:
> 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.

This would be quite rare, typically business databases collect inserts, 
for example an order entry system, so you do say 50 inserts a day, then 
it does a bunch of deletes (say all records over 1 year old), then 
starts collecting again.

An application has 2 possible actions here, it could simply reuse the 
space as it goes or an application might, backup the database, do the 
deletes, do a compress, returning the space to the OS, then continue on.

For a client/server database it's probably better to do the first, and 
let the DBA deal with maintenance.  For stand alone databases, it's 
probably better to take the second option.


View raw message