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,
-Rick
Any information will be greatly appreciated.
Brett
