X

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

Advanced Compression option - Common Questions

Gregg Christman
Product Manager

In this blog we’re going to discuss some
of the most common, user asked, questions related to Advanced Row Compression,
these questions include:


- How to enable Advanced Row Compression for existing tables and partitions?

-
Which
applications (OLTP or Data Warehouse) are best for Advanced Row Compression?


- What
types of tables shouldn’t be compressed?

For existing
tables and partitions, there are three best practice approaches to enabling
Advanced Row Compression:

- ALTER TABLE … ROW STORE COMPRESS ADVANCED

Enables Advanced
Row Compression for all future DML -- however, the existing data in the table
will remain uncompressed.

- Online Redefinition (DBMS_REDEFINITION)

Enables Advanced
Row Compression for future DML and also compress existing data - keeps the
table online for both read/write activity during the migration. See here for more information regarding the restrictions that apply to the online redefinition
of tables.

- ALTER TABLE … MOVE ROW STORE COMPRESS ADVANCED


Enables Advanced
Row Compression for future DML and also compress existing data.  The ALTER TABLE...MOVE statement does not permit DML against
the table while the statement is executing.


So you may be asking, which is best for
my organization? Well, it depends. If you want to keep tables and partitions
online and available while compression is being enabled, then using online
redefinition would be best. If you are taking some downtime to compress, then
use ALTER TABLE MOVE – but no matter which is used both will provide the same
level of compression.

For users of Oracle Database 12c, the Automatic
Data Optimization (ADO) feature of Advanced Compression can be used to compress
a table or partition in place (no moving of the table or partition needed).
We’ll discuss this new way to compress in a future blog, so stay tuned.

Advanced Row Compression is ideally
suited for both OLTP and data warehouse applications. This is important, to
keep in mind, as we begin to discuss (in future blogs) using Advanced Row
Compression (and Hybrid Columnar Compression) to deploy a compression tiering
solution.

When talking with users, and asked if
they should compress all the tables and partitions in their database, I tell
them that the general recommendation is to compress all the tables in the
database with one exception: if the table is used as a queue, i.e. rows are
inserted into the table, then later deleted, then more rows are inserted then
deleted -- you shouldn't compress a table used for this purpose.

Much more information regarding Advanced
Compression best practices is available. Please see this Oracle Advanced
Compression white
paper
(pages 8 and 9) for more information.

The database storage optimization
adventure continues in the next blog, in which we will discuss similar
questions related to Hybrid Columnar
Compression.

Join the discussion

Comments ( 2 )
  • guest Wednesday, June 22, 2016

    You said you were going to discuss this:

    How to enable Advanced Row Compression for existing tables and partitions?

    But you only discussed this:

    How to enable Advanced Row Compression for existing tables?

    Where is the info on partitions?


  • guest Wednesday, June 22, 2016

    Good catch, I mistakenly didn't include the partition information. I will cover that specific topic in July.

    Thanks for pointing this out!

    gregg


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