db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Behaviour of SYSCS_COMPRESS_TABLE
Date Tue, 31 May 2005 16:35:23 GMT
While running SYSCS_COMPRESS_TABLE on a particular table, no operations
can be done on that particular table or it's indexes.  Operations on
other tables and indexes are not affected, so work can be done
concurrently by other threads on other tables.

Note that cloudscape automatically reuses space from deleted rows when
new rows are inserted into the table.  The main problem
SYSCS_COMPRESS_TABLE is solving is if there are a number of deletes
which will not be followed by a number of inserts.  The reuse of space
is not as efficient as the compress table at it squeezes every last bit
of free space out, and returns that space to the OS.

I don't have numbers, but the compress table operation is a very
expensive operation.  It basically scans every row in the table and
inserts every non-deleted row into a new table.  And then rebuilds all
existing indexes on the new table, finally followed by deleting the old
files associated with the old tables and indexes.


Sten Nordström wrote:

> We are usimg the embedded variant of derby to handle various persistence 
> requirements. This means that we do a lot of insert and delete operations, 
> quickly growing the size of the on-disk files. I have been looking into using 
> the SYSCS_COMPRESS_TABLE procedure in order to compress the database, since the 
> applications can run for months, we want to avoid filling up the disk with data 
> that is not needed anymore.
> Have I understood correctly, that while running SYSCS_COMPRESS_TABLE no other 
> operations can be done on the database? 
> Are there any numbers on how database size and number of active rows versus 
> deleted ones, influence the time it takes to run the compress operation?
> Best Regards,
> -- sten

View raw message