Paul Taylor <[email protected]> writes: > Knut Anders Hatlen wrote: >> Paul Taylor <[email protected]> writes: >> >> >>> Knut Anders Hatlen wrote: >>> >>>> Paul Taylor <[email protected]> writes: >>>> >>>> >>>>> HI, I use derby in embedded mode, I let the user specify a maxmium >>>>> size for the derby database files, my program monitors this and if it >>>>> goes over the size I use SQL to delete records that are no longer >>>>> necessary in the hope of shrinking the database files size, but it >>>>> doesn't, how can I force Derby to shrink back down ? >>>>> >>>> Hi Paul, >>>> >>>> The database files won't shrink unless you compress the tables. >>>> >>>> http://db.apache.org/derby/docs/10.6/ref/rrefaltertablecompress.html >>>> http://db.apache.org/derby/docs/10.6/ref/rrefproceduresinplacecompress.html >>>> >>>> Hope this helps, >>>> >>>> >>> Thanks guys that does help, the only problem is that it takes an >>> exclusive lock on the table and I wanted to run it in the background >>> as and when required when other tasks might be going ahead. But can >>> probaly work round this. >>> >> >> In the issue tracker there's a request for allowing more concurrent >> activity while compressing a table, but there hasn't been any work on it >> yet. https://issues.apache.org/jira/browse/DERBY-3974 >> >> > I dont quote get the difference between the two procedures, one makes > adjustments in place, the other doesnt, but they both exclusively lock > the table so what is the advantage of one over the other ?
Yes, they both lock the table exclusively. The main advantage of the in-place variant is that it requires less disk space during the operation, since it doesn't create a new copy of the table. SYSCS_COMPRESS_TABLE, on the other hand, will also update the index cardinality statistics while it's compressing the table, whereas the in-place variant doesn't update the statistics. And I think there are some edge cases where it will be able to free more space than in-place. -- Knut Anders
