In an earlier
blog I discussed enabling compression for existing tables but failed to discuss
enabling compression on partitions. So in this blog we’re going to briefly
examine the ways in which users can enable compression for partitions while
partitions are online. Sorry if this covers some materials we’ve already looked
at, but I wanted to bring this topic together within a single blog for you.
For users of
Oracle Database 12c, the Online Move Partition to Any Compressed Format
feature, one of the many lesser known features included with the Advanced
Compression option, can be used to enable compression, for partitions, online.
You can use the
ALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITION
statement to move a table partition or subpartition. When you use the ONLINE
keyword with either of these statements, DML operations can continue to run
uninterrupted on the partition or subpartition that is being moved. When you
include the UPDATE INDEXES clause, these statements maintain both local and
global indexes during the move.
Please note that
you don’t need Advanced Compression for any of what I just mentioned.
But here’s what
you need to know about what Advanced Compression does provide:
Certain uses of
Online Move Partition require the Advanced Compression option: specifically, if
the feature is used to move a partition or subpartition to a compressed format
including Advanced Row Compression and Hybrid Columnar Compression.
Another way to
enable compression, for an existing partition, is to use Online Redefinition
(DBMS_REDEFINITION) which also keeps table/partitions online for both
read/write activity during the migration. Online redefinition will clone the
indexes to the interim table during the operation. All the cloned indexes are
incrementally maintained during the sync (refresh) operation so there is no
interruption in the use of the indexes during, or after, the online
redefinition. The exception is when online redefinition is used for redefining
a partition -- any global indexes are invalidated and need to be rebuilt after
the online redefinition. See here
for more information regarding the restrictions that apply to the online
redefinition of tables/partitions.
So if you’re
using Oracle Database 12c, and you want to enable compression for partitions
online, then consider using the Online Move Partition to Any Compressed Format
capability. If you are using Oracle Database 11g and want to enable compression
for partitions online, then please consider using Online Redefinition.
But for now the
database storage optimization adventure continues my next blog, in which we
will discuss Advanced Network Compression, another of the lesser known features
of Advanced Compression.