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>
