ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;
ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS FOR ARCHIVE LOW;
If you use the
MOVE statement, then the local indexes for partition
sales_q1_1998 become unusable. You must rebuild them afterward, as follows:
ALTER TABLE sales MODIFY PARTITION sales_q1_1998 REBUILD UNUSABLE LOCAL INDEXES;
You can also include the
UPDATE INDEXES clause in the
MOVE statement in order for the entire operation to be completed automatically without any negative effect on users accessing the table.
The following statement merges two existing partitions into a new, compressed partition, residing in a separate tablespace. The local bitmap indexes have to be rebuilt afterward, as in the following:
ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998 INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998 COMPRESS FOR OLTP UPDATE INDEXES;
For more details and examples for partition management operations, refer to Partition Administration.
Oracle Database Performance Tuning Guide for details regarding how to estimate the compression ratio when using table compression
Oracle Database SQL Language Reference for the SQL syntax
Oracle Database Concepts for more information about Hybrid Columnar Compression. Hybrid Columnar Compression is a feature of certain Oracle storage systems.
Oracle Database Administrator's Guide for information about changing the compression level using online redefinition