Major Description
Each derby table or index is stored in a separate file. Space from
deleted rows is eventually reclaimed within the file as is used for
subsequent inserts into the same file. That space is not returned to
the OS unless the user calls the SYSCS_UTIL.SYSCS_COMPRESS_TABLE
system procedure. That procedure will return the unused space in
the tables and indexes to the OS. It gets an exclusive lock on the
table, copies all rows in the indexes and the base table into new
compressed files and delete the old files. Prior to jdk 1.4 this was
the only way to return space from a file to the OS.
In jdk 1.4 RandomAccessFile was enhanced to allow the truncation of a
file, which would return the space at the "end" of the file back to
the OS. In order to take advantage of this new feature a new
compress feature is needed in derby.
The assumption is that this work will be used in future work which will
automatically schedule this job and others in background, with no
interaction needed from the dba. The 1st phase of this work will
simply build a procedure that will do the work. The 2nd phase will
be to look into scheduling the procedure automatically as part of
the current background post commit processing. Longer term it would
be best if this fit into a new background task monitor, which could
schedule larger background tasks balanced against the other priorities
of the system. These tasks might include: this new online compress,
automatic statistics gathering, more proactive deleted row reclamation, ....
The proposed feature will reorganize base tables and indexes, moving
empty pages to the "end". It will release space back to the operating
system when it has created a chunk of empty pages at the end of the
file. It will be designed to run in background, and will lock resources
of the table for as short a time as possible so that it can iteratively
process the table.
To reclaim space in the heap, it will scan the heap in page reverse order.
It will get a short term table lock, process all the rows on a page, and
then commit that transaction releasing the lock. The commit will be
optimized like other internal transactions, and will not need to wait
for a synchonized write. Each row moved, will require all the index
entries for that row to also be updated. While doing the processing it
will also take care of processing committed deleted rows. When space
is free at the end of the table it will be freed back to the operating
system, using the RandomAccessFile.setLength() interface.
To reclaim space in the btree, data on pages will be moved rather than
rows. Data from pages at the end of the file will be moved to free
smaller numbered pages. Again short term table locks will be required,
and the operation will look similar to the current btree merge operations
already implemented. Again when a chunk of pages is free at the end of
the file, they will be returned to the OS using the same mechanism as
the heap.