db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Question about TRUNCATE TABLE and freeing disk space
Date Tue, 29 Mar 2011 13:25:13 GMT
On 3/29/11 5:02 AM, Bergquist, Brett wrote:
> I have to do a poor man’s partitioning because I have a table where 
> records are constantly being added (right now about 1.7 million per 
> day) and I need to purge older records out. My plan is to partition 
> incoming records into separate tables for a week and then to drop a 
> week’s worth of data by dropping and recreating a table. I also have a 
> view that consolidates the data across the various weeks back into a 
> single view.
> I see the TRUNCATE TABLE command available in Derby 10.7 and was 
> wondering if it has the same ability to free disk space as dropping 
> and recreating a table or if it will still suffer the problem where 
> the disk space is still allocated until a compact is run? It would be 
> nice to use the TRUNCATE TABLE because I would not have to destroy and 
> re-create my view but if it does not have the same benefits, I will go 
> with the dropping and recreating the table and view.
Hi Brett,

I ran the following experiments.

o I created an empty database. This left me with 71 files in seg0.

o I created two tables and put a row in each one. This bumped the number 
of seg0 files up to 73.

o I truncated the first table. This bumped the number of seg0 files up 
to 74.

o I dropped the second table. This did not alter the number of seg0 
files: the count remained 74.

o I recreated the second table. This bumped the file count to 75.

o I compressed the first table. This bumped the file count to 76.

o I compressed the second table. This bumped the file count to 77.

o I checkpointed the database. This dropped the file count down to 73.

I believe the following is true:

1) When you TRUNCATE a table, you get a new, empty file just like you do 
when you drop and recreate a table. From the point of view of running 
subsequent queries, TRUNCATE table is equivalent to DROP/CREATE.

2) However, the old file is still hanging around (just as it is when you 
drop a table). The space is not reclaimed from the file system.

3) Table compression also creates a new file and does not delete the old 
file. Table compression does not release space to the file system--this 
operation actually claims more space.

4) Space is reclaimed from the file system when you perform a checkpoint.

Hope this pushes the discussion forward,
> Any information will be greatly appreciated.
> Brett

View raw message