Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7109

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE doesn't work as expected

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.14.2.0, 10.15.2.0
    • None
    • Store
    • None
    • Normal
    • Seen in production
    • Important

    Description

      The SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure seems to be unable to reclaim any disk space under almost all circumstances. I have a number of existing DBs that I tried to reorganize to reclaim disk space using the mentioned procedure. Because that didn't even free the smallest amount of space, I started a number of tests and it looks like inplace compression simply doesn't work, at all.

      Example:

      One of the DBs uses ~37 GB of disk space and the exact same amount after running SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. After using SYSCS_UTIL.SYSCS_COMPRESS_TABLE to reorganize the DB it only uses 20,4 GB of disk space. This might be correct, if I correctly understand how inplace compression should work - but is not really what I expected. But even after I completely deleted a number of tables and removed more than 90% of the rows from all other tables of the original DB, SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was unable to reclaim any disk space. In contrast, SYSCS_UTIL.SYSCS_COMPRESS_TABLE now reduced the DB size to ~ 130MB. To carry this to the extreme, I completely removed any data from all tables. As expected, procedure SYSCS_UTIL.SYSCS_COMPRESS_TABLE now results in an empty DB only taking up ~4 MB of disk space, while SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was still unable to reclaim a relevant amount of disk space (only ~ 4,6GB), leaving a completely empty DB still occupying more than 32 GB of disk space.

      In all calls to SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE I set PURGE_ROWS, DEFRAGMENT_ROWS, and TRUNCATE_END to 1.

      Attachments

        1. DerbyTest.java
          7 kB
          Holger Rehn

        Activity

          People

            Unassigned Unassigned
            ickzon Holger Rehn
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: