Just a heads up, I have recently went through this exercise of having a table that is always being inserted at one end and deleted at the other. Network test results were being inserted at a rate of about 4 million per day and the same amount from earlier days needed to be purged out. I had major performance issues in trying to delete rows while inserting rows. Turns out I could insert faster than delete. And on top of that, the space was not being reused efficiently enough and I had no down time to run the compress procedures.
What I ended up doing was to do data partitioning. I ended up creating a table for each week of the year and used the Restrict VTI functions to build a "view" combining all of the tables using UNION back into one virtual table, and then using "truncate table" to purge a whole week of data in a few seconds. The Restricted VTI was used because of the ability to optimize the returned rows based on the incoming query constraints. Not perfect as it is a poor man's partitioning but it does work. You might consider this route if you have no downtime to delete and compress. Just some thoughts Brett -----Original Message----- From: Sundar Narayanaswamy [mailto:sundar...@yahoo.com] Sent: Saturday, October 29, 2011 1:02 AM To: derby-user@db.apache.org Subject: Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE question 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 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). > > -- View this message in context: http://old.nabble.com/SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE-question-tp32736560p32742387.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
