db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein Grovlen - Sun Norway <Oystein.Grov...@Sun.COM>
Subject Re: Does in-place compress really defragment?
Date Mon, 03 Apr 2006 10:00:55 GMT
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. 
>           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 

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:

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

My case indicates that the table is not defragmented.


View raw message