db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Manjula G Kutty <manjula.ku...@gmail.com>
Subject Re: Does in-place compress really defragment?
Date Fri, 31 Mar 2006 18:11:16 GMT
Ø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> 
>
>
>  
>
Hi,

I have used inplace compression in my test , which is a java program,. I 
called inpleace compression every one hour intervel and I'm deleting 
rows randomly. So I'm sure that they are not getting deleted from the 
bottom of the table. I 'm seeing notable increase in the free space 
after using inplace compression.

Thanks
Manjula

Mime
View raw message