In this blog, we will revisit Advanced Row Compression, to discuss what not to compress. Please note that the use of Advanced Row Compression requires Advanced Compression.

In terms of what not to compress, there is a recommendations that I typically make to anyone using, or looking to use Advanced Row Compression:

  • If a table/partition is used as a queue, i.e. rows are inserted into the table, then later deleted, then more rows are inserted then deleted you should not compress a table used for this purpose

The Oracle Documentation indicates the following restrictions related to compression:

19.2.6.10 Notes and Restrictions for Compressed Tables (see here)

Advanced row compression, warehouse compression, and archive compression are not supported for the following types of tables:

    • Index-organized tables
    • External tables
    • Tables with LONG and LONG RAW columns
    • Temporary tables
    • Tables with ROWDEPENDCIES enabled
    • Clustered tables

Also, please note that in Advanced Compression with Oracle Database 11g, there is a limitation where OLTP Table Compression (renamed to Advanced Row Compression in Oracle Database 12c) is not supported for use with tables/partitions that had more than 255 columns (this limit was removed in Oracle Database 12c).

Here are some additional best practice suggestions (insights) for Advanced Row Compression:

  • Space usage reduction with Advanced Row Compression gives the best results where the most duplicate data is stored (low cardinality). Sorting data (on the columns with the most duplicates) prior to bulk loads may increase the compression ratio (at the expensive of additional load preparation)
  • Larger blocks do not always ensure higher Advanced Row Compression ratios. Testing with your own data is the best way to determine if larger/smaller block sizes will have an impact on your compression ratio
  • LOBs over 4k in size are best stored out-of-line with SecureFiles, and if an organization has licensed Advanced Compression, they can use Advanced LOB Compression to potentially reduce the amount of storage required for LOBs
  • Although CPU overhead is typically minimal, implementing Advanced Row Compression is ideal on systems with available CPU cycles, as compression will have additional, although minor overhead for some DML operations

Please see the document below for more information about Advanced Row Compression as well as more compression best practices.

Advanced Compression