I posted the following to the users list but saw no response, so I thought I would post here as it may be more relevant to developers.
After some testing, it seems that Derby is reusing the space of deleted records before allocating more space from the file system. If this is the case then what use does the call:
call syscs_util.syscs_inplace_compress_table(SCHEMA,TABLE, 1, 0, 0);
have? Basically what does the PURGE_ROWS option do that is above and beyond what is being done by Derby already to reuse the space of deleted records?
Also after testing we are seeing the following. With a database with no deleted rows, my test application is inserting about 150 records/second into a table. I let this run for about 2 million records and the insert rate is consistent. Now I purge out 1.5 million records and run the test again. The insert rate is now about 25 records/second. Running the above compress with the PURGE_ROWS option and rerun the test and still about 25 records/second. Run full SYSCS_UTIL.SYSCS_COMPRESS_TABLE and rerun the test and the insert rate is back to 150 records/second.
The reduced insert rate because of deleted records is very much a problem. We have a table that gets about 700K records inserted per day and purges out 30 days old data at about 700K records per day. This has a great effect on our insert rate. Why the big hit because of deleted records and can anything other than a compress help? This process has no downtime so running a compress can only be done maybe once a month.
Thanks for an feedback.