In this blog
we’re going back to revisit Advanced Row Compression and discuss what not to
compress. The use of Advanced Row Compression requires the Advanced Compression
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.
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:
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).
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’m 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
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.
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
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