db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Reclame unused space, but how exactly?
Date Sun, 06 Mar 2016 16:53:10 GMT
> 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



Mime
View raw message