I am thinking about this issue, thank you for reporting.
Sundar Narayanaswamy wrote:
I have posted the issue to DERBY-5487. I have also attached the Java test
program.
The test rows do insert at one end of the primary key and delete the other
end.. Interestingly, I noticed that primary key space is reclaimed if I
reuse the primary keys across the insert-delete loops. But, my application
This is not surprising, it confirms that in general the reclaim space at
split time works well for uniform type index distributions.
Your application is the 2nd worst case for Derby. I don't know if we
can fix at row level, but there may be some extra work we can do to try
and get table level locks and do page merging more often and/or in
inplace compress. For your specific application would it work if
inplace compress got table level locks during the purge phase?
The worst case for Derby would be a data distribution of an index which
resulted in one row on each leaf. There is not support for merging
non-empty leaf pages other than full offline compress. Anyone know if
this case is handled in other databases?
requires me to use continuously increasing primary keys (not reuse them).
Mike Matrigali wrote:
Posting your test to a JIRA issue would be best. It would be
interesting to post the space table results after each
insert/delete/compress iteration (or every 10, ...).
When do you commit (every row or every 10000)? Is it multi-threaded?
Does your
test always insert rows at one end of the index and delete them
from the other end. If so it may be DERBY-5473 (a runtime issue,
not a compress table issue).
