Example of Table Compression and Partitioning

The following statement moves and compresses an existing partition sales_q1_1998 of table sales:

ALTER TABLE sales
  MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;

Alternatively, you could choose Hybrid Columnar Compression (HCC), as in the following:

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.

See Also: