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.
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 0 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.
