Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=sbcglobal.net;
  h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=XiVJxO2GZGyHzqZKA4Pa/nByMXPUsI5X/T8IVF2MZFDUsgGCaC7svQauwk12lKTmTJ0EGN+ULJ5yu5s2+9nmich75dBIsh1y7XU8Z1aM/wZMXUpuPNPKcU1e73uOFyTMfVHQvbnpOmy4/0a8la5I/dy4DHw6OxR9io9aGhh1LA8=
  ;
Message-ID: <4EAED637.1090309@sbcglobal.net>
Date: Mon, 31 Oct 2011 10:09:11 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE question
References: <1319776014.2665.YahooMailNeo@web112411.mail.gq1.yahoo.com>
 <4EAB0D80.504@sbcglobal.net> <32742387.post@talk.nabble.com>
In-Reply-To: <32742387.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I am thinking about this issue, thank you for reporting.

Sundar Narayanaswamy wrote:
> I have posted the issue to DERBY-5487. I have also attached the Java test
> program. 
> 
> The test rows do insert at one end of the primary key and delete the other
> end.. Interestingly, I noticed that primary key space is reclaimed if I
> reuse the primary keys across the insert-delete loops. But, my application
This is not surprising, it confirms that in general the reclaim space at
split time works well for uniform type index distributions.

Your application is the 2nd worst case for Derby.  I don't know if we 
can fix at row level, but there may be some extra work we can do to try
and get table level locks and do page merging more often and/or in
inplace compress.  For your specific application would it work if 
inplace compress got table level locks during the purge phase?

The worst case for Derby would be a data distribution of an index which
resulted in one row on each leaf.  There is not support for merging 
non-empty leaf pages other than full offline compress.  Anyone know if 
this case is handled in other databases?

> requires me to use continuously increasing primary keys (not reuse them).
> 
> 
> Mike Matrigali wrote:
>> Posting your test to a JIRA issue would be best.  It would be 
>> interesting to post the space table results after each
>> insert/delete/compress iteration (or every 10, ...).
>> When do you commit (every row or every 10000)?  Is it multi-threaded? 
>> Does your
>> test always insert rows at one end of the index and delete them
>> from the other end.  If so it may be DERBY-5473 (a runtime issue,
>> not a compress table issue).
>>
>>
>