Bryan Pendleton wrote:
Øystein Grøvlen wrote:
I tried an experiment with on-line compress and it seems like no space
is freed unless I delete records at the end of the heap:
It does seem like the documentation allows for this:
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 in committed.
SYSCS_INPLACE_COMPRESS
attempts to reclaim space within the same table, but cannot
guarantee
it will recover all available space.
Did you try your same experiment with full compress?
No, since that is quite a different mechanism where effectively a new
table is created, the data is moved, and the old table dropped. I was
interested in exploring interactions with in-place compress and holdable
cursors.
According to the documentation for in-place compress, I would have
assumed that I should have been able to release space regardless of
where in the file there are free space when I specify both PURGE_ROWS
and DEFRAGMENT_ROWS in addition to TRUNCATE_END. From the documentation:
PURGE_ROWS
If PURGE_ROWS is set to a non-zero value, then a single pass is
made through the table which will purge committed deleted rows from the
table. This space is then available for future inserted rows, but
remains allocated to the table. As this option scans every page of the
table, its performance is linearly related to the size of the table.
DEFRAGMENT_ROWS
If DEFRAGMENT_ROWS is set to a non-zero value, then a single
defragment pass is made which will move existing rows from the end of
the table towards the front of the table. The goal of defragmentation is
to empty a set of pages at the end of the table which can then be
returned to the operating system by the TRUNCATE_END option. It is
recommended to only run DEFRAGMENT_ROWS if also specifying the
TRUNCATE_END option. The DEFRAGMENT_ROWS option scans the whole table
and needs to update index entries for every base table row move, so the
execution time is linearly related to the size of the table.
TRUNCATE_END
If TRUNCATE_END is set to a non-zero value, then all contiguous
pages at the end of the table will be returned to the operating system.
Running the PURGE_ROWS and/or DEFRAGMENT_ROWS options may increase the
number of pages affected. This option by itself performs no scans of the
table.
My case indicates that the table is not defragmented.
--
Øystein
