I appreciate your taking the time to help me out here Rick. I am not seeing the same thing in production with Derby 10.5.x in regards to the space being released back to the file system however. I currently have a single table that contains all of the records and one problem is that I cannot delete records fast enough without affecting insert rate and also it does not free enough pages for space to be re-used efficiently so the database is growing without bounds. Compact is no help as the database needs to run 24/7 with no breaks for maintenance and compact locks up the table.
That being said, I wrote a utility application for emergency maintenance that creates a new table that is a mirror of the existing table, copies 5 days worth of records to it, drops the original table, and renames the new table back to the original name and re-creates the constraints and indexes. This has the immediate effect of releasing the spaces that was allocated to the original table back to the file system. Last night this was run on a database that was taking 186Gb of disk space and immediately brought this down to 46Gb of disk space. I do not have a "checkpoint" in my utility application. I will run this again to verify what I am seeing, but I believe this to be true. Right now we are running 10.5.x in production, so I guess I will have to do a test setup with 10.7.x and experiment as well. I was just hoping that it was something missing in the documentation on the effect of TRUNCATE TABLE. -----Original Message----- From: Rick Hillegas [mailto:[email protected]] Sent: Tuesday, March 29, 2011 9:25 AM To: [email protected] Subject: Re: Question about TRUNCATE TABLE and freeing disk space 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 >
