Common Partition Maintenance Operations in OLTP Environments

Using either a DROP or TRUNCATE operation removes older data based on the partitioning key criteria. The drop operation removes the data and the partition metadata, while a TRUNCATE operation removes only the data but preserve the metadata. All local index partitions are dropped respectively, and truncated. Asynchronous global index maintenance is done for partitioned or nonpartitioned global indexes and is fully available for select and DML operations.

The following example drops all data older than January 2006 from the orders_oltp table. As part of the drop statement, an UPDATE GLOBAL INDEXES statement is executed, so that the global index remains usable throughout the maintenance operation. Any local index partitions are dropped as part of this operation.

ALTER TABLE orders_oltp DROP PARTITION p_before_jan_2006
 UPDATE GLOBAL INDEXES;

Using a MOVE or MERGE operation as part of an Information Lifecycle Management (ILM) strategy, you can relocate older partitions to the most cost-effective storage tier. Using the ALTER TABLE ONLINE MOVE functionality enables the data to be available for both queries and DML operations. Local indexes are maintained and likely relocated as part of the merge or move operation. The standard index maintenance is done for partitioned or nonpartitioned global indexes and is fully available for select and DML operations.

The following example shows how to merge the January 2006 and February 2006 partitions in the orders_oltp table, and store them in a different tablespace. Any local index partitions are also moved to the ts_low_cost tablespace as part of this operation. The UPDATE INDEXES clause ensures that all indexes remain usable throughout and after the operation, without additional rebuilds.

ALTER TABLE orders_oltp
  MERGE PARTITIONS p_2006_jan,p_2006_feb
  INTO PARTITION p_before_mar_2006 COMPRESS
  TABLESPACE ts_low_cost
  UPDATE INDEXES;

For more information about the benefits of partition maintenance operations for Information Lifecycle Management, see Managing and Maintaining Time-Based Information.