db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert Kam" <moonblade.w...@gmail.com>
Subject Table Compression and FreeSpace Claiming
Date Fri, 27 Jun 2008 12:16:34 GMT
Dear Derby ..

I'm using 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 ?

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 was hoping something like vacuum in postgresql =p, which reclaims the
whole database, and not per table basis ..

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

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