db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Table Compression and FreeSpace Claiming
Date Fri, 27 Jun 2008 13:02:44 GMT
Hi Albert,

Some comments inline...

Albert Kam wrote:
> Dear Derby ..
>
> I'm using 10.4.1.3 <http://10.4.1.3>
>
> I tried out a simple experiment ..
> 1. Fired out ij from the shell and connects to a derby database
> 2. Created a table, named sms_out
> 3. Inserted some data from a csv file using SYSCS_UTIL.SYSCS_IMPORT_TABLE
>
> 4. I deleted the data
> ij> delete from sms_out;
> 61 rows inserted/updated/deleted
>
> 5. I tried issuing select to confirm this ..
> ij> select * from APP.sms_out;
> ID         |RECIPIENT                                    
> |TEXT                                                                               
                                            
> |DISPATCH_DATE             |STATUS_REP&|FLASH_SMS  |SRC_PORT   
> |DST_PORT   |VALIDITY_P&
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 rows selected
>
> 6. I tried to compress the empty table using 
> SYSCS_UTIL.SYSCS_COMPRESS_TABLE, hoping that the deleted data that 
> still uses some space in the harddrive will be reclaimed as stated in 
> the derbyref.pdf.
> ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'sms_out', 1);
>
> And this resulted in error :
> ERROR 38000: The exception 'java.sql.SQLException: 'ALTER TABLE' 
> cannot be performed on 'APP.sms_out' because it does not exist.' was 
> thrown while evaluating an expression.
> ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'APP.sms_out' 
> because it does not exist.
>
> I then did show tables from app, and sms_out is really there ..
>
> Did i do something wrong ?
The problem is that routine arguments are case-sensitive. Your table 
name needs to be upper-cased. Try this:

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'SMS_OUT', 1);

You are wrestling here with the issue of unquoted vs. quoted 
identifiers. For more context on this topic, please see the section in 
the Derby Reference Manual titled "SQL identifiers": 
http://db.apache.org/derby/docs/10.4/ref/
>
> And is this a wrong approach to reclaiming freespace ?
>
> Imagine that i've insert around ten thousands of data to a table, and 
> then i drop the table .. Now i cant compress that already non-existent 
> table to reclaim freespace ..
I'm not sure what problem you're anticipating here. The unclaimed free 
space is in the file which is the backing store for the table. Dropping 
the table causes Derby to delete the whole file. There is no free space 
to reclaim at this point. Please be patient with me if I am not 
understanding your question.
>
> I was hoping something like vacuum in postgresql =p, which reclaims 
> the whole database, and not per table basis ..
I'm not aware of a built-in command for compressing all of your tables 
at once. However, it should be fairly easy to write a database procedure 
which does this.
>
> Hmm .. at the end of this email, i just remembered that after dropping 
> or deleting the table, i havent shutdown the derby. Perhaps doing 
> shutdown first and the freespace is automatically reclaimed ? =D
I don't think so. You have to explicitly ask Derby to reclaim the free 
space. That's the intention of what's being said in the first paragraph 
of the section titled "SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure" 
in the Derby Reference Manual.

Hope this helps,
-Rick
>
> Regards,
> Albert Kam
>
> -- 
> Do not pursue the past. Do not lose yourself in the future.
> The past no longer is. The future has not yet come.
> Looking deeply at life as it is in the very here and now,
> the practitioner dwells in stability and freedom.
> (Thich Nhat Hanh) 


Mime
View raw message