Hi Gerrit,

I don't have a theory about what caused this problem. Maybe COMPRESS_TABLE() has a serious bug. A couple questions:

Q1) Do you have the results of SPACE_TABLE() for this situation?

Q2) What value did you specify for the SEQUENTIAL argument of COMPRESS_TABLE()?

Q3) Other than COMPRESS_TABLE(), what is the pattern of activity on this table?

If this happens again, take a look at the ALTER TABLE DROP PRIMARY KEY and ALTER TABLE ADD CONSTRAINT commands. They may make it simpler to repair the damage.


On 9/15/20 5:14 AM, Hohl, Gerrit wrote:
Hello everyone,

we use Apache Derby v10.14.2.0 in our software and now have a problem with an 

Every night we perform a compact on all tables - at least the ones which can be 
String, Smallint)).
Normally that constellation works fine.
But now faced an installation which ran out of free disk space without any 

After some analysing we narrowed it down to one table which is meant for 
Records are inserted and deleted often (the size of it is limited at 10.000 
entries through the software).
The maximum - based on the structure - should be around 40 MB. Not really much.
So we were very surprised seeing that this thing took around 14 GB.

I realized that I accumulated the table and its indexes to get that value.
After splitting it up, I saw that the table itself really only took 40 MB.
But two of the 6 indexes - one was the primary key index - took more than 13 GB 
of space.
Apache Derby also said no NUMFREEPAGES and no ESTIMSPACESAVING.

My solution then was shutting down the service (in-process database), connect 
to the database, dropping the indexes, create a new column for temporarily 
storing the primary key value, copying the values, also dropping that column, 
recreating the primary key column, copying everything back, dropping the 
temporary primary key column, creating all the indexes again.
Now everything is back to normal and the table *including* its indexes is 40 MB.

Any idea on this? Why did the index grow that big? What can I do preventing it?
Seems reorganising isn't an option as Apache Derby itself doesn't realize that 
it can free space.


Reply via email to