Hello everyone, we use Apache Derby v10.14.2.0 in our software and now have a problem with an installation.
Every night we perform a compact on all tables - at least the ones which can be shrunk (SYSCS_DIAG.SPACE_TABLE() and SYSCS_UTIL.SYSCS_COMPRESS_TABLE(String, String, Smallint)). Normally that constellation works fine. But now faced an installation which ran out of free disk space without any reason. After some analysing we narrowed it down to one table which is meant for logging. 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. Regards, Gerrit
