<< Obtaining locking information | Trademarks >>

Reclaiming unused space

<< Obtaining locking information | Trademarks >>

Derby Server and Administration Guide

Reclaiming unused space

A Derby table or index (sometimes called a conglomerate) can contain unused space
after large amounts of data have been deleted or updated.

This happens because, by default, Derby does not return unused space to the operating
system. After a page has been allocated to a table or index, Derby does not automatically
return the page to the operating system until the table or index is dropped, even if the
space is no longer needed. However, Derby does provide a way to reclaim unused space
in tables and associated indexes.

If you determine that a table and its indexes have a significant amount of
unused space, use either the SYSCS_UTIL.SYSCS_COMPRESS_TABLE or
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure to reclaim that
space. SYSCS_COMPRESS_TABLE is guaranteed to recover the maximum amount
of free space, at the cost of temporarily creating new tables and indexes before the
statement is committed. SYSCS_INPLACE_COMPRESS attempts to reclaim space
within the same table, but cannot guarantee it will recover all available space. The
difference between the two procedures is that unlike SYSCS_COMPRESS_TABLE, the
SYSCS_INPLACE_COMPRESS procedure uses no temporary files and moves rows
around within the same conglomerate.

You can use the SYSCS_DIAG.SPACE_TABLE diagnostic table to estimate the
amount of unused space in a table or index by examining, in particular, the values of the
NUMFREEPAGES and ESTIMSPACESAVING columns. For example:

SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'FLIGHTAVAILABILITY'))
AS T

For more information about SYSCS_DIAG.SPACE_TABLE, see "SYSCS_DIAG
diagnostic tables and functions" in the Derby Reference Manual.

As an example, after you have determined that the FlightAvailability table and its related
indexes have too much unused space, you could reclaim that space with the following
command:

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 0);

The third parameter in the SYSCS_UTIL.SYSCS_COMPRESS_TABLE() procedure
determines whether the operation will run in sequential or non-sequential mode. If you
specify

for the third argument in the procedure, the operation will run in non-sequential

mode. In sequential mode,Derby compresses the table and indexes sequentially, one
at a time. Sequential compression uses less memory and disk space but is slower. To
force the operation to run in sequential mode, substitute a non-zero SMALLINT value for
the third argument. The following example shows how to force the procedure to run in
sequential mode:

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 1);

For more information about this command, see the Derby Reference Manual.