db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Me <m.v.lunte...@gmail.com>
Subject Re: Reclame unused space, but how exactly?
Date Sun, 06 Mar 2016 20:28:25 GMT
I wonder if you realize the only way Derby (and other darabase systems) gives space back to
the OS is when it's freed up a page. Typically data is not restructured to that extent on
an update. So if a page initially had 3 rows because each row had lots of data in one column,
after update, the page will still have 3 rows, even if those rows are now a fraction of the
size. It wain't try to consolidate across pages. It might use empty space - if any - to insert
new rows, but will hold on to the page it now has under its control. Note also that depending
on the datatype the space in the 'emptied' column may still be reserved, or padded with blanks,
not really empty after all.
It's possible compress actually compresses the data on the pages, probably depends on the

But reorganizing means doing it for all the rows in a table, so it takes some time. 

If in your experiments you find compress doesn't get you enough space back you could think
of having two tables, one for the initial data, one for the modified one.


On Mar 6, 2016, at 8:53 AM, Bryan Pendleton <bpendleton.derby@gmail.com> wrote:

>> I want to make the unused space availabe to derby for the insertion of new
>> rows. It should be optinal to give unused space back to the OS. In order to do
>> so, I have to use some system utility.
> I'm not totally sure whether the 5th word in the above line is "optional"
> or "optimal".
> To return unused space to the OS, you have to make a special call;
> simply deleting your rows from the table with DELETE FROM is not enough,
> nor is updating your rows to set every column to the shortest-possible
> value.
>> The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is
>> this correct?
> It is correct that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure
> will return space to the operating system if there is space available.
> There is also the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure.
> And there is the DROP TABLE statement.
> And the TRUNCATE TABLE statement.
>> Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the
>> OS, this is not helpfull either. Or is it?
> I don't understand your question: I thought that you **wanted** to "give unused
> space back to the OS".
>> Where is my misunderstanding, i.e. what do I have to do, in order do reclame
>> the space?
>> Or does derby somehow optimize disk usage in the described case automatically?
> Derby does many such optimizations. You might want to start by reading
> the archives of derby-user from May-June, 2005, specifically starting here:
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/%3Cloom.20050531T105713-104@post.gmane.org%3E
> Maybe you could run some experiments, to try different approaches,
> and report back to the list about what experiments you have run,
> and what you have found, and the list could help you understand
> the behaviors you are seeing.
> Note that if you actually have "a huge number of rows", where
> the data you store in each row is "large, say, on average of about
> 9000 characters" and then you "replace the data and they are
> on average only 10 characters long", you have created a somewhat
> mysterious program, to me.
> Will you ever make those values longer again? Or will they permanently
> be only 10 characters long? Why did you need the 9000 byte values in
> the first place? Does this happen with every row in your table, or
> is it more of a mixture of some rows growing and some rows shrinking?
> The wonderful thing about a database like Derby is that you have many
> choices in how you build your application, and Derby will work very
> hard to try to accommodate whatever choices you make.
> The challenging thing about a database like Derby is that you have many
> choices in how you build your application, and Derby will work very
> hard to try to accommodate whatever choices you make.
> thanks,
> bryan

View raw message