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 (asf.osuosl.org: 32.97.110.149 is neither permitted nor
 denied by domain of Stan.Bradbury@gmail.com)
Message-ID: <44298770.50009@gmail.com>
Date: Tue, 28 Mar 2006 10:58:56 -0800
From: Stanley Bradbury <Stan.Bradbury@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: How do I tell whether a table has unused space or not?
References: <4428A545.7060006@amberpoint.com>
In-Reply-To: <4428A545.7060006@amberpoint.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bryan Pendleton wrote:

> Recently, I had a table which was able to substantially
> benefit from compression. I determined that I had a problem
> by setting derby.logQueryPlan to TRUE, running a "select *"
> query against the table, and then viewing the page and
> row count statistics that were emitted in the query plan,
> where I saw that the optimizer was expecting to process many
> more pages than I thought needed to be in the table.
>
> Compressing the table made my problem go away, and the
> optimizer returned to choosing my desired query plans.
>
> But I am left with the desire for an easier way to figure
> out whether my table needs compression or not.
>
> I tried reading the manual, for example I found
> http://db.apache.org/derby/docs/10.1/adminguide/cadminspace21579.html
> but that page does not offer any clear way to tell whether a
> table needs to be compressed or not.
>
> What is the preferred way to decide whether a table has unused space?
>
> thanks,
>
> bryan
>
>
Hi Bryan -
You can use the diagnostic method SpaceTable to find out how many pages 
are allocated to a table and obtain an estimate of how much space might 
be saved by running  the COMPRESS_TABLE procedure (column: 
ESTIMSPACESAVING).  The method takes a SCHEMA name (optional) and a 
TABLE name.  The query looks like this:

select * from NEW org.apache.derby.diag.SpaceTable('SCHEMANAME', 
'MY_TABLENAME') as ST;

EXAMPLE:
ij> select * from NEW 
org.apache.derby.diag.SpaceTable('SYS','SYSTABLES') as ST;
CONGLOMERATENAME
        |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        
|NUMUNFILLEDPAGES    |PAGESIZE   |ESTIMSPACESAVING
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SYSTABLES_INDEX1
        |1     |1                   |0                   
|1                   |4096       |0
SYSTABLES_INDEX2
        |1     |1                   |0                   
|1                   |4096       |0
SYSTABLES
        |0     |1                   |0                   
|1                   |4096       |0

3 rows selected

You might be interested in following the enhancement request *DERBY-512 
</jira/browse/DERBY-512> *this identifies the need to reclaim space 
automatically so COMPRESS_TABLE is no longer needed.