Using either a
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;
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
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.