---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/alter_table/indexes.md?version=v25.1
  - https://ydb-platform--ydb.viewer.diplodoc.com/ru/yql/reference/syntax/alter_table/indexes.md?version=v25.1
  - href: en/yql/reference/syntax/alter_table/indexes.md
    type: text/markdown
    title: Markdown version
  - href: ../../../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/yql/reference/syntax/alter_table/indexes.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt

# Adding, removing, and renaming a index


<!-- source: en/_includes/not_allow_for_olap_note.md -->
{% note warning %}

<!-- source: en/_includes/not_allow_for_olap_text.md -->
Supported only for [row-oriented](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#row-oriented-tables) tables. Support for [column-oriented](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#column-oriented-tables) tables is currently under development.
<!-- endsource: en/_includes/not_allow_for_olap_text.md -->

{% endnote %}
<!-- endsource: en/_includes/not_allow_for_olap_note.md -->

<!-- source: en/_includes/vector_index_limitations.md -->
{% note alert %}

The functionality of vector indexes is available in the test mode in main. This functionality will be fully available in version 25.1.

The following features are not supported:

* Index update: the main table can be modified, but the existing index will not be updated. A new index is to be built to reflect the changes. If necessary, the existing index can be [atomically replaced](https://ydb-platform--ydb.viewer.diplodoc.com/en/reference/ydb-cli/commands/secondary_index.md?version=v25.1#rename) with the newly built one.
* Building an index for vectors with [bit quantization](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/udf/list/knn.md?version=v25.1#functions-convert).

These limitations may be removed in future versions.

{% endnote %}
<!-- endsource: en/_includes/vector_index_limitations.md -->


## Adding an index {#add-index}

`ADD INDEX` — adds an index with the specified name and type for a given set of columns. Grammar:

```yql
ALTER TABLE `<table_name>`
  ADD INDEX `<index_name>`
    [GLOBAL|LOCAL]
    [SYNC|ASYNC]
    [USING <index_type>]
    ON ( <index_columns> )
    [COVER ( <cover_columns> )]
    [WITH ( <parameter_name> = <parameter_value>[, ...])]
  [,   ...]
```

<!-- source: en/yql/reference/syntax/_includes/index_grammar_explanation.md -->
* `GLOBAL/LOCAL` — global or local index; depending on the index type (`<index_type>`), only one of them may be available:
  
    * `GLOBAL` — an index implemented as a separate table or set of tables. Synchronous updates to such an index require distributed transactions.  
    * `LOCAL` — a local index within a shard of a row-oriented or column-oriented table. Does not require distributed transactions for updates, but does not provide pruning during search.

* `<index_name>` — unique index name that will be used to access data.
* `SYNC/ASYNC` — synchronous or asynchronous writes to the index, synchronous by default.
* `<index_type>` - index type, currently supported:

    * `secondary` — secondary index. Only `GLOBAL` is available. This is the default value.
    * `vector_kmeans_tree` — vector index. Described in detail in [Vector index](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/create_table/vector_index.md?version=v25.1).

* `<index_columns>` — comma-separated list of column names for the table being created. This list defines the composition and order of columns included in the index key. Must be specified. The index key will include both the columns listed and the columns from the table's primary key.
* `<cover_columns>` — comma-separated list of column names from the created table that will be saved in the index in addition to index key columns, providing the ability to get additional data without accessing the table. Empty by default.
* `<parameter_name>` and `<parameter_value>` — index parameters specific to a particular `<index_type>`. Some index parameters cannot be specified during index creation. See [Altering an index](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/alter_table/indexes.md?version=v25.1#alter-index).
<!-- endsource: en/yql/reference/syntax/_includes/index_grammar_explanation.md -->

Parameters specific to vector indexes:

<!-- source: en/yql/reference/syntax/_includes/vector_index_parameters.md -->
  * common parameters for all vector indexes:
    * `vector_dimension` - embedding vector dimensionality (16384 or less)
    * `vector_type` - vector value type (`float`, `uint8`, `int8`, or `bit`)
    * `distance` - distance function (`cosine`, `manhattan`, or `euclidean`), mutually exclusive with `similarity`
	  * `similarity` - similarity function (`inner_product` or `cosine`), mutually exclusive with `distance`
  * specific parameters for `vector_kmeans_tree` (see [Vector Index Type `vector_kmeans_tree`](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/vector-indexes.md?version=v25.1#kmeans-tree-type)):
    * `clusters` - number of centroids for k-means algorithm (values greater than 1000 may degrade performance)
    * `levels` - number of levels in the tree
<!-- endsource: en/yql/reference/syntax/_includes/vector_index_parameters.md -->


You can also add a secondary index using the YDB CLI [table index](https://ydb-platform--ydb.viewer.diplodoc.com/en/reference/ydb-cli/commands/secondary_index.md?version=v25.1#add) command.


### Examples

A regular secondary index:

```yql
ALTER TABLE `series`
  ADD INDEX `title_index`
  GLOBAL ON (`title`);
```

A vector index:

```yql
ALTER TABLE `series`
  INDEX emb_cosine_idx GLOBAL SYNC USING vector_kmeans_tree
  ON (embedding) COVER (title)
  WITH (
    distance="cosine",
    vector_type="float",
    vector_dimension=512,
    clusters=128,
    levels=2
  );
```

## Altering an index {#alter-index}

Indexes have type-specific parameters that can be tuned. Global indexes, whether [synchronous](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/secondary_indexes.md?version=v25.1#sync) or [asynchronous](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/secondary_indexes.md?version=v25.1#async), are implemented as hidden tables, and their automatic partitioning and followers settings can be adjusted just like those of regular tables.

{% note info %}

Currently, specifying secondary index partitioning settings during index creation is not supported in either the [`ALTER TABLE ADD INDEX`](#add-index) or the [`CREATE TABLE INDEX`](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/create_table/secondary_index.md?version=v25.1) statements.

{% endnote %}

```sql
ALTER TABLE <table_name> ALTER INDEX <index_name> SET <setting_name> <value>;
ALTER TABLE <table_name> ALTER INDEX <index_name> SET (<setting_name_1> = <value_1>, ...);
```

* `<table_name>`: The name of the table whose index is to be modified.
* `<index_name>`: The name of the index to be modified.
* `<setting_name>`: The name of the setting to be modified, which should be one of the following:

    * [AUTO_PARTITIONING_BY_SIZE](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#auto_partitioning_by_size)
    * [AUTO_PARTITIONING_BY_LOAD](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#auto_partitioning_by_load)
    * [AUTO_PARTITIONING_PARTITION_SIZE_MB](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#auto_partitioning_partition_size_mb)
    * [AUTO_PARTITIONING_MIN_PARTITIONS_COUNT](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#auto_partitioning_min_partitions_count)
    * [AUTO_PARTITIONING_MAX_PARTITIONS_COUNT](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#auto_partitioning_max_partitions_count)
    * [READ_REPLICAS_SETTINGS](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/datamodel/table.md?version=v25.1#read_only_replicas)


{% note info %}


These settings cannot be reset.

{% endnote %}

* `<value>`: The new value for the setting. Possible values include:
    * `ENABLED` or `DISABLED` for the `AUTO_PARTITIONING_BY_SIZE` and `AUTO_PARTITIONING_BY_LOAD` settings
    * `"PER_AZ:<count>"` or `"ANY_AZ:<count>"` where `<count>` is the number of replicas for the `READ_REPLICAS_SETTINGS`
    * An integer of `Uint64` type for the other settings

### Example

The query in the following example enables automatic partitioning by load for the index named `title_index` of the table `series`, sets its minimum partition count to 5, and enables one follower per AZ for every partition:


```yql
ALTER TABLE `series` ALTER INDEX `title_index` SET (
    AUTO_PARTITIONING_BY_LOAD = ENABLED,
    AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 5,
    READ_REPLICAS_SETTINGS = "PER_AZ:1"
);
```

## Deleting an index {#drop-index}

`DROP INDEX`: Deletes the index with the specified name. The code below deletes the index named `title_index`.

```yql
ALTER TABLE `series` DROP INDEX `title_index`;
```


You can also remove a index using the YDB CLI [table index](https://ydb-platform--ydb.viewer.diplodoc.com/en/reference/ydb-cli/commands/secondary_index.md?version=v25.1#drop) command.


## Renaming an index {#rename-index}

`RENAME INDEX`: Renames the index with the specified name.

If an index with the new name exists, an error is returned.


Replacement of atomic indexes under load is supported by the command [ydb table index rename](https://ydb-platform--ydb.viewer.diplodoc.com/en/reference/ydb-cli/commands/secondary_index.md?version=v25.1#rename) in the YDB CLI and by YDB SDK ad-hoc methods.


Example of index renaming:


```yql
ALTER TABLE `series` RENAME INDEX `title_index` TO `title_index_new`;
```