The compression techniques that are used for Database In-Memory compression are different from what are used for on-disk compression. On-disk compression is designed to reduce the overall space usage for table segments, while the compression algorithms used for Database In-Memory are designed for query performance. Database In-Memory queries can operate directly on the compressed format. Due to the different techniques used, it is not uncommon for table segments to have different compression sizes in-memory versus on-disk.

It is also not uncommon to get better compression on-disk as compared to in-memory compression when using the QUERY levels of compression.
Although columnar formats generally have the advantage of potentially better compression because of bigger in-memory compression unit sizes as compared to Oracle database block sizes, and values for the same column (datatype) represented together tend to compress better. But remember that every column in the column store is compressed using its own dictionary because every column is stored in a separate column structure.

Advanced Row compression on the other hand (part of Oracle Advanced Compression Option) has it’s own advantages in this respect:

  1. With on-disk compression using Advanced Row compression we can share the dictionary across all the columns in the table. That is, every database block has a local dictionary, but that dictionary is shared across all the rows and columns in the block. This can give a substantial improvement in compression ratios.
  2. Advanced Row compression supports multi-column dictionaries. That is, each symbol in the dictionary can represent more than one column from the table. Single dictionary references can encode more than one column for a row.
  3. Column reordering and recursive dictionary to create bigger and longer column multi-column symbols. Column reordering often gives a substantial boost to compression ratios.

Compression in general it is very difficult to predict because it is so data dependent. It is entirely possible to get better compression from on-disk compression than from in-memory compression, but the reverse is also true.

Original publish date: June 3, 2016