Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of manjula.kutty@gmail.com
 designates 64.233.184.232 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
        b=bfXuGWOS0/rJDu2/WbTh5tJt004YR6RstMyyeVdPy1qOt48p6RcnrYvqJYUvZCt+zIMDYE700H11EXi6E1BuXYsNseOsQN9He2+7ONjdGrRTi7GKAqAHFSh40qGkMZbjaXWK6cVH6vYgG5oqNNG07KHCfTDOKTZDxCpnmw+Uhxo=
Message-ID: <442D70C4.6010801@gmail.com>
Date: Fri, 31 Mar 2006 10:11:16 -0800
From: Manjula G Kutty <manjula.kutty@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.5) Gecko/20041217
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: Does in-place compress really defragment?
References: <0IWZ00J4WWUAYO@epost-mail1.sweden.sun.com>
In-Reply-To: <0IWZ00J4WWUAYO@epost-mail1.sweden.sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

�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