db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øystein Grøvlen <Oystein.Grov...@Sun.COM>
Subject Does in-place compress really defragment?
Date Fri, 31 Mar 2006 14:13:22 GMT

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> 


Mime
View raw message