If the partition contains data and global indexes, use one of the following methods (method 1, 2, or 3) to truncate the table partition.
Method 1
Leave the global indexes in place during the ALTER TABLE TRUNCATE PARTITION statement. In this example, table sales has a global index sales_area_ix, which is rebuilt.
ALTER TABLE sales TRUNCATE PARTITION dec98; ALTER INDEX sales_area_ix REBUILD;
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
Method 2
Run the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE TRUNCATE PARTITION statement. The DELETE statement updates the global indexes, and also fires triggers and generates redo and undo logs.
For example, to truncate the first partition, run the following statements:
DELETE FROM sales PARTITION (dec98); ALTER TABLE sales TRUNCATE PARTITION dec98;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Method 3
Specify UPDATE INDEXES in the ALTER TABLE statement. This causes the global index to be truncated at the time the partition is truncated.
ALTER TABLE sales TRUNCATE PARTITION dec98
UPDATE INDEXES;
With asynchronous global index maintenance, this operation is a metadata-only operation.