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
