If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods (method 1, 2 or 3) to drop the table partition.
PARTITION statement without maintaining global indexes. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked
UNUSABLE. The following statements provide an example of dropping partition
dec98 from the
sales table, then rebuilding its global nonpartitioned index.
ALTER TABLE sales DROP PARTITION dec98; ALTER INDEX sales_area_ix REBUILD;
sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate
REBUILD statement for each partition in the index. The following statements rebuild the index partitions
ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix; ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix; ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix; ... ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. While asynchronous global index maintenance keeps global indexes valid without the need of any index maintenance, you must use the
INDEXES clause to enable this new functionality. This behavior ensures backward compatibility.
DELETE statement to delete all rows from the partition before you issue the
PARTITION statement. The
DELETE statement updates the global indexes.
For example, to drop the first partition, issue the following statements:
DELETE FROM sales partition (dec98); ALTER TABLE sales DROP PARTITION dec98;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
INDEXES in the
TABLE statement. Doing so leverages the new asynchronous global index maintenance. Indexes remain valid.
ALTER TABLE sales DROP PARTITION dec98 UPDATE INDEXES;