ALTER INDEX

The ALTER INDEX command modifies the definition of an index.

Syntax

ALTER INDEX index_name
    alter_option;

alter_option depends on the operation you want to perform on the index. For all supported clauses, see the sections below.

Clause

RENAME TO

ALTER INDEX index_name
    RENAME TO new_name;

Parameter or clause	Description
RENAME TO	This clause changes the name of the index. If the index is associated with a table constraint (either UNIQUE, PRIMARY KEY, or EXCLUDE), the constraint is renamed as well. There is no effect on the stored data.
new_name	The new name of the index.

-- Change the name of the index "idx_1" to "idx_2"
ALTER INDEX idx_1 RENAME TO idx_2.

SET PARALLELISM

ALTER INDEX index_name
SET PARALLELISM { TO | = } parallelism_number;

Parameter or clause	Description
SET PARALLELISM	This clause controls the degree of parallelism for the targeted streaming job.
parallelism_number	Can be ADAPTIVE or a fixed number (e.g., 1, 2, 3). Setting it to ADAPTIVE expands the job’s parallelism to all available units, while a fixed number locks it at that value. Setting it to 0 is equivalent to ADAPTIVE. The maximum allowed value is determined by the max_parallelism of the job. For more information, see Configuring maximum parallelism.

-- Set the parallelism of the index "idx_orders" to 3.
ALTER INDEX idx_orders SET PARALLELISM = 3;

SET BACKFILL_PARALLELISM

ALTER INDEX index_name
    SET BACKFILL_PARALLELISM { TO | = } parallelism_value [ DEFERRED ];

This statement controls the degree of parallelism used during the backfill phase of an index. It lets you tune resource usage for backfilling independently from the index’s steady-state parallelism.

Parameter or clause	Description
SET BACKFILL_PARALLELISM	Sets the parallelism for the backfill phase of the streaming job.
parallelism_value	Can be ADAPTIVE or a fixed number (e.g., 1, 2, 3). Setting it to ADAPTIVE or 0 expands backfill parallelism to all available units. A fixed number locks it at that value.
DEFERRED	Optional. When specified, the change takes effect after the current backfill completes, without interrupting an ongoing backfill. If omitted, the change is applied immediately.

-- Set backfill parallelism to a fixed value
ALTER INDEX idx_orders SET BACKFILL_PARALLELISM = 4;

-- Set backfill parallelism to adaptive
ALTER INDEX idx_orders SET BACKFILL_PARALLELISM = ADAPTIVE;

-- Defer the parallelism change until the current backfill completes
ALTER INDEX idx_orders SET BACKFILL_PARALLELISM = 4 DEFERRED;

SET RESOURCE_GROUP

ALTER INDEX index_name
    SET RESOURCE_GROUP { TO | = } { DEFAULT | resource_group_name } [ DEFERRED ];

ALTER INDEX index_name
    RESET RESOURCE_GROUP [ DEFERRED ];

Use this statement to change the resource group used by the index’s streaming job.

Parameter or clause	Description
SET RESOURCE_GROUP	Assigns the index to a specific resource group.
RESET RESOURCE_GROUP	Resets the index to the default resource group.
resource_group_name	The target resource group.
DEFERRED	Optional. Defers applying the resource group change.

-- Assign the index to a resource group
ALTER INDEX idx_orders SET RESOURCE_GROUP TO high_priority;

-- Reset the index to the default resource group
ALTER INDEX idx_orders RESET RESOURCE_GROUP DEFERRED;