Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management
-
Advanced Compression
May 27, 2016
Online Move Partition to Any Compressed Format - A Lesser Known Feature of Advanced Compression
In this blog
we’re going to discuss the Online Move Partition to Any Compressed Format
feature, one of the many lesser known features included with the Advanced
Compression option.
Here are some key
points:
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.
If you do not
include the ONLINE keyword, then DML operations are not permitted on the data
in the partition or subpartition until the move operation is complete. 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 Advanced Compression: specifically, if the
feature is used to move a partition or subpartition to a compressed format
including Basic, Advanced Row, or Hybrid Columnar Compression.
So remember, if
you’re using Advanced Compression don’t forget about the added capability you
have when using this feature, you can compress your partitions during the move.
To see my short
(about 3 minutes) video on the Online Move Partition to Any Compressed Format
feature, please click
here (ACO OTN page).
But for now the
database storage optimization adventure continues in my next blog, in which we
will compare Advanced Compression and hardware-based compression.
Very userful information. What about "alter table ... move nocompress" that is not part of any extra cost option?
Kind regards,
Kjell Tore Aspeslåen
Using "ALTER TABLE ... MOVE NOCOMPRESS" does not require the Advanced Compression option.
Thanks for reading my blog.
gregg