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 <[EMAIL PROTECTED]> 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.
