db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Database maintenance routines
Date Tue, 05 Nov 2019 13:57:00 GMT
On 11/5/19 4:03 AM, Alex O'Ree wrote:
> I have a use case where by I add a bunch of rows, export then in an
> archive, then delete all the rows and repeat for weeks or months on end.
> Are there any maintenance procedures I should be running after each purge?
> Postgres and mssql has some functions to tell it to recover disk space and
> compact itself, or to notify that there was a large change in row volume.
> Is there anything like this in derby?
I would recommend using TRUNCATE TABLE to delete all the rows of the 
table. This should be fast and it will re-initialize the table with an 
empty backing file.

In general, you can use the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system 
procedure to squeeze unused space out of a table. You can use 
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS to regenerate the histograms which 
the optimizer uses in order to calculate optimal query plans.

See the Derby Reference Manual for information on these commands.

Hope this helps,

View raw message