Database maintenance routines

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Database maintenance routines

Alex O'Ree
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?

Reply | Threaded
Open this post in threaded view
|

Re: Database maintenance routines

Rick Hillegas-3
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,
-Rick

Reply | Threaded
Open this post in threaded view
|

Re: Database maintenance routines

Alex O'Ree
Perfect, thanks

On Tue, Nov 5, 2019 at 8:57 AM Rick Hillegas <[hidden email]> wrote:
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,
-Rick