X

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

Online Move Partition to Any Compressed Format - A Lesser Known Feature of Advanced Compression

Gregg Christman
Product Manager

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.

Join the discussion

Comments ( 2 )
  • Kjell Tore Aspeslåen Friday, July 15, 2016

    Very userful information. What about "alter table ... move nocompress" that is not part of any extra cost option?

    Kind regards,

    Kjell Tore Aspeslåen


  • Gregg Christman Friday, July 15, 2016

    Using "ALTER TABLE ... MOVE NOCOMPRESS" does not require the Advanced Compression option.

    Thanks for reading my blog.

    gregg


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