X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

Advanced Row Compression – What Not to Compress (and Some Best Practice Insights)

Gregg Christman
Product Manager

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
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’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
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).

Join the discussion

Comments ( 2 )
  • Fairlie Rego Sunday, May 1, 2016

    Hi Greg

    Is the below ER being implemented in 12.2

    Bug 14079401 : CUSTOMERS NEED A WAY OF DISABLING THE ADVANCED COMPRESSION FEATURE

    Thanks

    Fairlie


  • Gregg Christman Wednesday, May 4, 2016

    Hi Fairlie,

    I'm not aware of any changes that would enable the Advanced Compression feature to be disabled.

    Best regards,

    gregg


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.