In this blog we are going back to revisit Advanced Row Compression and discuss what not to compress. Note: The use of Advanced Row Compression requires the Advanced Compression
option.

In terms of what not to compress, here are some recommendations that I typically make to anyone using, or looking to use Advanced Row Compression, these include:

1) Advanced Row Compression is NOT supported for use with tables that have LONG data types,

2) If a table/partition is used as a queue, i.e. rows are inserted then deleted and this is done repeatedly, then that table /partition is not a candidate for Advanced Row Compression, and

3) Advanced Row Compression is also not supported on tables with row dependencies and on clustered tables.

In Advanced Compression with Oracle Database 11g, there had been a limitation where OLTP Table Compression (renamed to Advanced Row Compression in Oracle Database 12c)
was not supported for use with tables/partitions that had more than 255 columns (this limitation was removed for conventional operations in 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 don’t always ensure higher Advanced Row Compression ratios. Testing with your own data is suggested if you want to determine if larger/smaller block sizes
will have an impact on your compression ratio.

LOBs are best stored in SecureFiles, and if an organization has licensed the Oracle Advanced Compression option, they can use Advanced LOB Compression and Deduplication to
potentially reduce the amount of storage required for LOBs.

I am  also often asked if an organization should consider implementing compression at the tablespace level (Advanced Row Compression can be implemented at the partition,
table or tablespace level). Regarding whether or not to compress at the Tablespace level:

For custom applications, I typically suggest compressing at the Tablespace level, but organizations should consider turning off compression on very high traffic or very small tables, such as tables used as queues.

For commercial packaged applications, where typically the number of objects can be very large, I typically suggest object selection instead of exclusion. Often the top
hundred largest tables and indexes consume the majority of the database space. Compressing those objects, while excluding high traffic objects like tables used as queues, will give the majority of the compression benefits. Other objects can be compressed over time as
needed.

But for now the database storage optimization adventure continues in the next blog, in which we will discuss, Online Move Partition to Any Compressed Format, a lesser known
feature of Advanced Compression (but one you will want to know about if you use partitioning).