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?
applications (OLTP or Data Warehouse) are best for Advanced Row Compression?
types of tables shouldn’t be compressed?
tables and partitions, there are three best practice approaches to enabling
Advanced Row Compression:
- ALTER TABLE … ROW STORE COMPRESS ADVANCED
Row Compression for all future DML -- however, the existing data in the table
will remain uncompressed.
- Online Redefinition (DBMS_REDEFINITION)
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
- ALTER TABLE … MOVE ROW STORE COMPRESS 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
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
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