there is a lot more work that could be done on the inplace compress,
my guess is that the defragment part is not finding space on the 1/3
empty pages to move the rows to. The defragment loop basically takes
rows at the "end" of the table and inserts them where the existing
technology tells them there is space (after being reset to look from
beginning rather than end). It is true that after the defragment
run the only space that can be returned to the OS is the free pages
at the "end", but the defragment run tries to move rows from end to
beginning.
There are a lot of smarter thing that could be done, creating an
in memory data structure with efficient access to number of free
bytes per page could then be used to do a better job of filling
"half" empty pages.
I am most concerned about 7 to 8, that should reduce space assuming
the 171 rows don't all fit on a page.
one question, what sizes of rows are you using (basically are there
actually 300 characters in your "c" column), for quick tests I always
use char columns as they use whatever space is declared no matter
the data. Varchar uses variable length storage. It would be
interesting to throw in a select from the space table vti after every
statement to see what is going on - see
opensource/java/testing/org/apache/derbyTesting/functionTests/tests/store/SpaceTable.sql
test for some usage.
For major space changes in the table I would recommend the offline
compress, it guarantees to reclaim ALL possible space in tables and
indexes and has the benefit of probably getting better clustering
on disk.
Ø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:
1. Deleted every third record of a table
2. Inline compress with purge&defragment. File size did not change
3. Deleted every second of the remaining records
4. Inline compress with purge&defragment. File size did not change
5. Deleted the last third of the remaining records
6. Inline compress with purge&defragment. File size reduced by 1/3.
7. Deleted first half of the remaining records
8. Inline compress with purge&defragment. File size did not change
Is this how it is supposed to be? I would have thought that each
compress would defragment the table and free space, but it seems like
only empty space at the end of a table is freed. Trace of what I did
below. (There are 1536 records in t. The records have primary keys
in range [0,1535] and was inserted in sorted order on primary key.
For all records j==mod(i,3).)
--
Øystein
ij> create table t1 (i integer primary key, j integer, c varchar(300));
0 rows inserted/updated/deleted
ij> insert into t1 select * from t;
1536 rows inserted/updated/deleted
ij> delete from t1 where j=1;
512 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
0 rows inserted/updated/deleted
ij> delete from t1 where j=2;
512 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
0 rows inserted/updated/deleted
ij> delete from t1 where i > 1024;
170 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
0 rows inserted/updated/deleted
ij> delete from t1 where i < 512;
171 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
0 rows inserted/updated/deleted
ij>
