> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Manipulating Data Skipping Indices

# Manipulating Data Skipping Indices

The following operations are available:

<h2 id="add-index">
  ADD INDEX
</h2>

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] ADD INDEX [IF NOT EXISTS] name expression TYPE type [GRANULARITY value] [FIRST|AFTER name]` - Adds index description to tables metadata.

<h2 id="drop-index">
  DROP INDEX
</h2>

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] DROP INDEX [IF EXISTS] name` - Removes index description from tables metadata and deletes index files from disk. Implemented as a [mutation](/reference/statements/alter/index#mutations).

<h2 id="materialize-index">
  MATERIALIZE INDEX
</h2>

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] MATERIALIZE INDEX [IF EXISTS] name [IN PARTITION partition_name]` - Rebuilds the secondary index `name` for the specified `partition_name`. Implemented as a [mutation](/reference/statements/alter/index#mutations). If `IN PARTITION` part is omitted then it rebuilds the index for the whole table data.

<h2 id="clear-index">
  CLEAR INDEX
</h2>

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] CLEAR INDEX [IF EXISTS] name [IN PARTITION partition_name]` - Deletes the secondary index files from disk without removing description. Implemented as a [mutation](/reference/statements/alter/index#mutations).

The commands `ADD`, `DROP`, and `CLEAR` are lightweight in the sense that they only change metadata or remove files.
Also, they are replicated, syncing indices metadata via ClickHouse Keeper or ZooKeeper.

<Note>
  Index manipulation is supported only for tables with [`*MergeTree`](/reference/engines/table-engines/mergetree-family/mergetree) engine (including [replicated](/reference/engines/table-engines/mergetree-family/replication) variants).
</Note>

<h2 id="concurrent-alter-and-multi-clause-materialize-index">
  Concurrent `ALTER` and multi-clause `MATERIALIZE INDEX`
</h2>

On replicated tables, rapid separate `ALTER`s against one table can raise `CANNOT_ASSIGN_ALTER` (code 517) when previous `ALTER`s have not yet been applied on the replica (metadata still behind — can remain true after an earlier alter was already assigned). This is a general concurrent metadata-`ALTER` / mutation condition (not mutation-only); serialize/retry, wait for prior mutation-producing alters via [`mutations_sync`](/reference/settings/session-settings/mutations#mutations_sync) / `is_done` in [`system.mutations`](/reference/system-tables/mutations), or combine independent metadata operations into one multi-clause `ALTER` when the grammar allows it. See [Synchronicity of ALTER Queries](/reference/statements/alter/index#synchronicity-of-alter-queries) and [Concurrent ALTER assignment](/reference/statements/alter/index#concurrent-alter-assignment-on-one-table).

Multiple `MATERIALIZE INDEX` clauses can appear in one `ALTER`. The covered case in-tree is packing several `ADD INDEX` clauses together with `MATERIALIZE INDEX` for those same new indexes in a single statement (`tests/queries/0_stateless/02911_add_index_and_materialize_index.sql`). That packed form is for ordinary (non-`DatabaseReplicated`) databases — `DatabaseReplicated` rejects mixed `ADD INDEX` + `MATERIALIZE INDEX` segments with `QUERY_IS_PROHIBITED`. Materialize-only multi-clause forms on already-existing indexes follow the same metadata-snapshot prepare path in the current implementation, but that exact shape is not yet covered by a focused stateless test—treat it as current implementation behavior rather than a separately guaranteed contract until such coverage exists. For ordered apply, issue one `MATERIALIZE INDEX` per statement and wait with [`mutations_sync`](/reference/settings/session-settings/mutations#mutations_sync).