Partial indexes let you build a secondary index on selected partitions rather than an entire partitioned table. Use ALTER TABLE ... ALTER INDEX to add, rebuild, or drop a partial index on specific partitions or subpartitions.
Syntax
All three operations share the same base structure:
ALTER TABLE table_name ALTER INDEX index_name
{ ADD | REBUILD | DROP } PARTITION partition_name0 [, partition_name1, ...];| Clause | Description |
|---|---|
ADD PARTITION |
Adds the partial index to the specified partitions. |
REBUILD PARTITION |
Rebuilds the partial index on the specified partitions. |
DROP PARTITION |
Removes the partial index from the specified partitions. |
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the partitioned table. |
index_name |
The name of the index to modify. |
partition_name0 |
The name of a partition or subpartition. To target multiple partitions or subpartitions, list their names separated by commas. |
Examples
Add, rebuild, and drop a partial index on partitions
The following examples use the orders table and the o_ind_amou index.
Add the o_ind_amou partial index to the orders_202212 partition:
ALTER TABLE orders ALTER INDEX o_ind_amou ADD PARTITION orders_202212;Rebuild the o_ind_amou partial index on the orders_202203, orders_202204, and orders_202205 partitions:
ALTER TABLE orders ALTER INDEX o_ind_amou REBUILD PARTITION orders_202203, orders_202204, orders_202205;Drop the o_ind_amou partial index from the orders_202201 and orders_202202 partitions:
ALTER TABLE orders ALTER INDEX o_ind_amou DROP PARTITION orders_202201, orders_202202;Add, rebuild, and drop a partial index on subpartitions
The following examples use the tenants table and the ind_date index.
Add the ind_date partial index to the p0_3 and p0_4 subpartitions:
ALTER TABLE tenants ALTER INDEX ind_date ADD PARTITION p0_3, p0_4;Rebuild the ind_date partial index on the p0_1 and p1_1 subpartitions:
ALTER TABLE tenants ALTER INDEX ind_date REBUILD PARTITION p0_1, p1_1;Drop the ind_date partial index from the p1_1 subpartition:
ALTER TABLE tenants ALTER INDEX ind_date DROP PARTITION p1_1;