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.

W