Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of rick.hillegas@oracle.com
 designates 148.87.113.117 as permitted sender)
Message-ID: <4F9FF1A1.7060403@oracle.com>
Date: Tue, 01 May 2012 07:22:25 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: "derby-dev@db.apache.org" <derby-dev@db.apache.org>
Subject: puzzling command options for
 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE()
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

According to the Derby Reference Guide, 
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE() is supposed to return file 
space to the operating system if you invoke it with the trailing 
TRUNCATE_END argument set to 1. I am puzzled by the behavior I am seeing.

I am looking at the repro for DERBY-5234. The heap conglomerate of a 
(soon to be) large table starts out as 8192 bytes long. Over time, the 
heap conglomerate grows to be 42672128 bytes long. Then all of the rows 
are deleted from the table and it is compressed with the following call:

  CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE( 'APP', 'OPERATIONS', 0, 
0, 1 )

The heap conglomerate shrinks a little, down to 41857024. The Reference 
Guide suggests that this way of invoking SYSCS_INPLACE_COMPRESS_TABLE() 
might not actually do much. Here's what the Reference Guide says: "To 
return the empty free space at the end of the same table, the following 
call will run much quicker than running all options but will likely 
return much less space." In this situation, that's an understatement. In 
this situation, the meaning of the call seems to be "Run fast but don't 
do what I want."

How is the user supposed to figure out which compression options should 
be turned on? Should we recommend that all options should always be 
turned on? Why would anyone want to run fast but not achieve much?

Thanks,
-Rick