Invalidating/Rebuilding Local Indexes of a Partition

• You can run ALTER INDEX PARTITION to invalidate or rebuild local indexes of a partition.
• You can run ALTER TABLE MODIFY PARTITION to invalidate or rebuild all indexes of a specified partition in a partitioned table.

For example, assume that the partitioned table range_sales has two local indexes range_sales_idx1 and range_sales_idx2, and the corresponding indexes on the partition date_202001 are range_sales_idx1_part1 and range_sales_idx2_part1.

The syntax for maintaining partitioned indexes of a partitioned table is as follows:

• Run the following command to disable all indexes on the date_202001 partition:

  ALTER TABLE range_sales MODIFY PARTITION date_202001 UNUSABLE LOCAL INDEXES;

• Alternatively, run the following command to disable the index range_sales_idx1_part1 on the date_202001 partition:

  ALTER INDEX range_sales_idx1 MODIFY PARTITION range_sales_idx1_part1 UNUSABLE;

• Run the following command to rebuild all indexes on the date_202001 partition:

  ALTER TABLE range_sales MODIFY PARTITION date_202001 REBUILD UNUSABLE LOCAL INDEXES;

• Alternatively, run the following command to rebuild the index range_sales_idx1_part1 on the date_202001 partition:

  ALTER INDEX range_sales_idx1 REBUILD PARTITION range_sales_idx1_part1;