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. /mikem 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 > >
