You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Paul Taylor <pa...@fastmail.fm> on 2010/09/08 12:50:38 UTC
Reclaiming space from Derby
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 ?
thanks Paul
Re: Reclaiming space from Derby
Posted by Kristian Waagan <kr...@oracle.com>.
On 08.09.10 12:50, Paul Taylor wrote:
> 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,
You have to run the compress system procedures. See these links for more
information:
http://db.apache.org/derby/docs/dev/ref/ref-single.html#rrefaltertablecompress
http://db.apache.org/derby/docs/dev/ref/ref-single.html#rrefproceduresinplacecompress
Hope this helps,
--
Kristian
>
> thanks Paul
Re: Reclaiming space from Derby
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Paul Taylor <pa...@fastmail.fm> writes:
> Knut Anders Hatlen wrote:
>> Paul Taylor <pa...@fastmail.fm> writes:
>>
>>
>>> Knut Anders Hatlen wrote:
>>>
>>>> Paul Taylor <pa...@fastmail.fm> 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
Re: Reclaiming space from Derby
Posted by Paul Taylor <pa...@fastmail.fm>.
Knut Anders Hatlen wrote:
> Paul Taylor <pa...@fastmail.fm> writes:
>
>
>> Knut Anders Hatlen wrote:
>>
>>> Paul Taylor <pa...@fastmail.fm> 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 ?
Paul
Re: Reclaiming space from Derby
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Paul Taylor <pa...@fastmail.fm> writes:
> Knut Anders Hatlen wrote:
>> Paul Taylor <pa...@fastmail.fm> 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
--
Knut Anders
Re: Reclaiming space from Derby
Posted by Paul Taylor <pa...@fastmail.fm>.
Knut Anders Hatlen wrote:
> Paul Taylor <pa...@fastmail.fm> 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.
Paul
Re: Reclaiming space from Derby
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Paul Taylor <pa...@fastmail.fm> 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,
--
Knut Anders