Invalidating/Rebuilding Local Indexes of a Partition

You can run ALTER INDEX PARTITION to invalidate or rebuild local indexes of a partition.
You can use ALTER TABLE MODIFY PARTITION to invalidate or rebuild all indexes of a specified partition in a partitioned table. If this syntax is applied to the partition of a level-2 partitioned table, this command takes effect on all level-2 partitions of the partition.

You can use ALTER TABLE MODIFY SUBPARTITION to invalidate or rebuild all indexes of a specified level-2 partition in a level-2 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;
```

Assume that the level-2 partitioned table list_range_sales has two local indexes: list_range_sales_idx1 and list_range_sales_idx2. The table has a partition channel1 and its level-2 partitions channel1_product1, channel1_product2 and channel1_product3. The indexes corresponding to level-2 partition channel1_product1 are channel1_product1_idx1 and channel1_product1_idx2.

The syntax for maintaining the partitioned indexes of a level-2 partitioned table is as follows:

Run the following command to disable all indexes on the level-2 partitions of partition channel1, including level-2 partitions channel1_product1, channel1_product2 and channel1_product3:
```
ALTER TABLE list_range_sales MODIFY PARTITION channel1 UNUSABLE LOCAL INDEXES;
```
Run the following command to rebuild all indexes on the level-2 partitions under partition channel1:
```
ALTER TABLE list_range_sales MODIFY PARTITION channel1 REBUILD UNUSABLE LOCAL INDEXES;
```

The syntax for maintaining the level-2 partitioned indexes of a level-2 partitioned table is as follows:

Run the following command to disable all indexes on the level-2 partition channel1_product1:
```
ALTER TABLE list_range_sales MODIFY SUBPARTITION channel1_product1 UNUSABLE LOCAL INDEXES;
```

Run the following command to rebuild all indexes on the level-2 partition channel1_product1:

ALTER TABLE list_range_sales MODIFY SUBPARTITION channel1_product1 REBUILD UNUSABLE LOCAL INDEXES;

Alternatively, run the following command to disable the index channel1_product1_idx1 on the level-2 partition channel1_product1:
```
ALTER INDEX list_range_sales_idx1 MODIFY PARTITION channel1_product1_idx1 UNUSABLE;
```
Run the following command to rebuild the index channel1_product1_idx1 on the level-2 partition channel1_product1:
```
ALTER INDEX list_range_sales_idx1 REBUILD PARTITION channel1_product1_idx1;
```