--- metadata: - name: generator content: Diplodoc Platform v5.50.6 alternate: - https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main - https://ydb.tech/docs/ru/yql/reference/syntax/alter_table/indexes.md?version=main sourcePath: en/core/yql/reference/syntax/alter_table/indexes.md --- > **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt # Adding, deleting, and renaming an index ## Adding an index {#add-index} `ADD INDEX` — adds an index with the specified name and type for the given set of columns in row tables. Grammar: ```yql ALTER TABLE `` ADD INDEX `` [GLOBAL|LOCAL] [UNIQUE] [SYNC|ASYNC] [USING ] ON ( ) [COVER ( )] [WITH ( = [, ...])] [, ...] ``` * `GLOBAL/LOCAL` — global or local index, depending on the index type (``), only one of them may be available: * `GLOBAL` — an index implemented as a separate table or a set of tables. Synchronous update of such an index requires distributed transactions. * `LOCAL` — a local index within a shard of a columnar or row-based table, does not require distributed transactions during update, but does not provide pruning during search. * `` — unique index name by which data can be accessed. * `SYNC/ASYNC` — indicator of index synchrony. * `SYNC` - [synchronous](https://ydb.tech/docs/en/concepts/query_execution/secondary_indexes.md?version=main#sync) index. Default value. * `ASYNC` - [asynchronous](https://ydb.tech/docs/en/concepts/query_execution/secondary_indexes.md?version=main#async) index. * `UNIQUE` — indicator of a [unique secondary index](https://ydb.tech/docs/en/concepts/query_execution/secondary_indexes.md?version=main#unique). A unique index must be global synchronous (`GLOBAL UNIQUE SYNC`) and must not contain the `USING ` construct. * `` — index type, currently supported: * `secondary` — secondary index. Only `GLOBAL` mode is available for secondary indexes. This is the default index type. * `vector_kmeans_tree` — vector index. Described in detail in [Vector index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/vector_index.md?version=main). * `fulltext_plain` — basic fulltext index. Described in detail in [Fulltext index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/fulltext_index.md?version=main). * `fulltext_relevance` — fulltext index with [BM25](https://en.wikipedia.org/wiki/Okapi_BM25) statistics for relevance scoring. Described in detail in [Fulltext index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/fulltext_index.md?version=main). * `json` — JSON index to speed up `JSON_EXISTS` and `JSON_VALUE` predicates on a column of type `Json` or `JsonDocument`. Described in more detail in [JSON-index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/json_index.md?version=main). * `bloom_filter` — local Bloom index. Available only for `LOCAL`. See [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom). * `bloom_ngram_filter` — local N-gram Bloom index. Available only for `LOCAL`. See [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom). * `min_max` — local min/max index. Available only for `LOCAL`. See [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-min-max). * `` — 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. * `` — 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. * `` and `` — index parameters specific to a particular ``. Some index parameters cannot be specified during index creation. See [Altering an index](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#alter-index). You can also add a secondary index using the [table index](https://ydb.tech/docs/en/reference/ydb-cli/commands/secondary_index.md?version=main#add) YDB CLI command. Parameters for all index types: * maximum number of `parallel` handlers based on [partitions](https://ydb.tech/docs/en/concepts/glossary.md?version=main#partition) involved in index building (an integer between `1` and `MaxBuildIndexShardsInFlight` from `SchemeShardConfig`). - If the parameter is not specified, the default value `32` or `MaxBuildIndexShardsInFlight` is currently used, whichever is smaller. `MaxBuildIndexShardsInFlight` defaults to `1000`. In future versions, the default parallelism selection logic may change. - You can set a lower limit to reduce the impact of index building on database performance. - You can also set a higher limit to speed up index building if you have enough hardware resources. Parameters specific to vector indexes: * common parameters for all vector indexes: * `vector_dimension` - embedding vector dimensionality (should be between 1 and 16384) * `vector_type` - vector value type (`float`, `uint8`, or `int8`) * `distance` - [distance function](https://ydb.tech/docs/en/yql/reference/udf/list/knn.md?version=main#functions-distance) (`cosine`, `manhattan`, or `euclidean`), mutually exclusive with `similarity` * `similarity` - [similarity function](https://ydb.tech/docs/en/yql/reference/udf/list/knn.md?version=main#functions-distance) (`inner_product` or `cosine`), mutually exclusive with `distance` * specific parameters for `vector_kmeans_tree` ([read more about the index type](https://ydb.tech/docs/en/dev/vector-indexes.md?version=main#kmeans-tree-type)): * `clusters` - number of centroids for k-means algorithm (should be between 2 and 2048) * `levels` - number of levels in the tree (should be between 1 and 16) * `overlap_clusters` - the number of nearest clusters to add each vector to (default 1) * `adaptive_clusters` - for [filtered indexes](https://ydb.tech/docs/en/dev/vector-indexes.md?version=main#filtered), automatically choose the number of clusters per filtering-column value based on how many vectors that value has (`true` or `false`, default `false`). See [Adaptive clusters](https://ydb.tech/docs/en/dev/vector-indexes-kmeans-tree-type.md?version=main#adaptive-clusters). * the total number of nodes in the tree, calculated as `clusters` raised to the power of `levels`, should be no more than 1073741824 * the product of `vector_dimension` and `clusters` should be no more than 4194304 {% note info %} For vector indexes, the `vector_type` and `vector_dimension` parameters can be omitted if the table is not empty — they are determined automatically from the row contents. The `levels` and `clusters` parameters are also determined automatically, and for them the table can be empty, but doing so is strongly not recommended because the default values in this case are `levels`=1, `clusters`=2; it is much better to create the index on a table that already has data loaded, so that the values can be correctly determined. {% endnote %} Parameters specific to full-text indexes: * common parameters for all fulltext indexes: * `tokenizer` - tokenizer type (`standard`, `whitespace`, or `keyword`) * `use_filter_lowercase` - lowercase filter (`true` or `false`) * `use_filter_length` - token length filter (`true` or `false`); when `true`, tokens shorter than `filter_length_min` or longer than `filter_length_max` are not indexed and are ignored during search * `filter_length_min` - minimum token length (positive integer); only applied when `use_filter_length=true` * `filter_length_max` - maximum token length (positive integer); only applied when `use_filter_length=true` * `use_filter_snowball` - [Snowball](https://snowballstem.org/) stemmer filter (`true` or `false`) * `language` - language for the [Snowball](https://snowballstem.org/) stemmer (for example, `english`, `russian`) * `use_filter_ngram` - [n-gram](https://en.wikipedia.org/wiki/N-gram) filter (`true` or `false`) * `use_filter_edge_ngram` - edge [n-gram](https://en.wikipedia.org/wiki/N-gram) filter (`true` or `false`) * `filter_ngram_min_length` - minimum n-gram length (positive integer) * `filter_ngram_max_length` - maximum n-gram length (positive integer) ### Parameters of local bloom indexes {#local-bloom} * `bloom_filter` * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) of the filter: the fraction of fragments that are not skipped even though the requested value is not there (range `(0, 1)`). A lower value reduces extra reads but increases index size. * If omitted, the default is `0.0001` for [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) and `0.1` for [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table). The stricter default on row-oriented tables matches OLTP point lookups; on column-oriented tables it reflects analytical scans, where a larger trade-off between index size and fragment skipping is acceptable. * Indexed columns: YQL types that support equality comparison, except `Yson`, `Json`, and `JsonDocument` (see [comparison operators](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#comparison-operators)). One column in a column-oriented table; multiple columns in a row-oriented table. On row-oriented tables, the indexed columns must form a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). * `bloom_ngram_filter` (`String` and `Utf8` columns; [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) only) * `ngram_size`: N-gram length, an integer from `3` to `8` (default `3`). * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) (range `(0, 1)`; default `0.1`). * `case_sensitive`: Whether n-grams respect character case: `true` or `false` (default `true`). ### Parameters of the local min_max index {#local-min-max} The min_max index has no specific parameters `WITH (...)`. ### Limitations The `ADD INDEX` operation for creating global secondary (`GLOBAL`, `UNIQUE`, etc.) and vector indexes is supported only for row tables. For [columnar tables](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables), only local indexes are supported via `ADD INDEX`: [bloom index](#local-bloom) and [min_max index](#local-min-max). Features of local bloom indexes: * The index is always [local](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) (`LOCAL`); there is no [global](https://ydb.tech/docs/en/concepts/glossary.md?version=main#secondary-index) variant. * Queries do not use the `VIEW ` syntax (unlike, for example, [fulltext indexes](https://ydb.tech/docs/en/dev/fulltext-indexes.md?version=main)). * The filter is applied on read only to data fragments where an index block was already stored during a write or [portion merge](https://ydb.tech/docs/en/concepts/glossary.md?version=main#compaction), other fragments are not skipped by this index until merge. * On [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table), `bloom_filter` is implemented as a **prefix bloom filter**: the indexed columns must form a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). The filter is built over that key prefix and accelerates point lookups and range scans that constrain the prefix. Multiple bloom indexes over the same prefix length are not allowed. {% note info "Limitations" %} * `COVER (...)` and extra index columns are not supported. * Column-oriented tables allow only one indexed column. Row-oriented tables allow multiple indexed columns. * `bloom_ngram_filter` is not supported on row-oriented tables. * On row-oriented tables, the indexed columns of a `bloom_filter` must be a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). Non-prefix column sets are rejected. * On row-oriented tables, two `bloom_filter` indexes cannot share the same prefix length (the same set of leading primary-key columns). {% endnote %} Features of the local min_max index: * The index is always [local](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) (`LOCAL`); there is no [global](https://ydb.tech/docs/en/concepts/glossary.md?version=main#secondary-index) variant. * Queries do not use the `VIEW ` syntax (unlike, for example, [full-text indexes](https://ydb.tech/docs/en/dev/fulltext-indexes.md?version=main)). * The filter is applied during reads only to data fragments for which the minimum and maximum values of the indexed column have already been computed and stored with the table data at write time or [merge](https://ydb.tech/docs/en/concepts/glossary.md?version=main#compaction). For other fragments, no skip by this index is performed. {% note info "Limitations" %} * Supported only for [columnar tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table). * `ON (...)` must specify exactly one column. * `COVER (...)` and additional data columns are not supported. * Specific parameters of `WITH (...)` are not supported. * `ALTER INDEX` is not supported for the min_max index. * Columns of types `Json` and `JsonDocument` are not supported. {% endnote %} ### Examples Secondary index: ```yql ALTER TABLE `series` ADD INDEX `title_index` GLOBAL ON (`title`); ``` [Vector index](https://ydb.tech/docs/en/dev/vector-indexes.md?version=main): ```yql ALTER TABLE `series` ADD INDEX emb_cosine_idx GLOBAL SYNC USING vector_kmeans_tree ON (embedding) COVER (title) WITH ( distance="cosine", vector_type="float", vector_dimension=512 ); ``` Full-text index: ```yql ALTER TABLE `series` ADD INDEX ft_idx GLOBAL USING fulltext_plain ON (title) WITH (tokenizer=standard, use_filter_lowercase=true); ``` [JSON index](https://ydb.tech/docs/en/dev/json-indexes.md?version=main): ```yql ALTER TABLE `series` ADD INDEX json_idx GLOBAL USING json ON (metadata); ``` [Bloom index](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main): ```yql ALTER TABLE `/Root/Table` ADD INDEX idx_bloom LOCAL USING bloom_filter ON (resource_id) WITH (false_positive_probability = 0.01); ``` Bloom n-gram index: ```yql ALTER TABLE `/Root/Table` ADD INDEX idx_ngram LOCAL USING bloom_ngram_filter ON (message) WITH ( ngram_size = 3, false_positive_probability = 0.01, case_sensitive = true ); ``` min_max index: ```yql ALTER TABLE `/Root/Table` ADD INDEX idx_created_at LOCAL USING min_max ON (created_at); ``` ## Changing index parameters {#alter-index} Indexes have type-dependent parameters that you can configure. Global indexes, [synchronous](https://ydb.tech/docs/en/concepts/query_execution/secondary_indexes.md?version=main#sync) or [asynchronous](https://ydb.tech/docs/en/concepts/query_execution/secondary_indexes.md?version=main#async), are implemented as hidden tables, and their automatic partitioning and replica parameters can be adjusted in the same way as regular table settings. {% note info %} Currently, setting partitioning parameters for secondary indexes when creating an index is not supported either in the [`ALTER TABLE ADD INDEX`](#add-index) statement or in the [`CREATE TABLE INDEX`](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=main) statement. {% endnote %} ```yql ALTER TABLE ALTER INDEX SET ; ALTER TABLE ALTER INDEX SET ( = , ...); ``` * `` - name of the table whose index needs to be changed. * `` - name of the index to change. * `` - name of the parameter to change. The set of allowed parameters depends on the index type: * for global secondary indexes: * [AUTO_PARTITIONING_BY_SIZE](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#auto_partitioning_by_size) * [AUTO_PARTITIONING_BY_LOAD](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#auto_partitioning_by_load) * [AUTO_PARTITIONING_PARTITION_SIZE_MB](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#auto_partitioning_partition_size_mb) * [AUTO_PARTITIONING_MIN_PARTITIONS_COUNT](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#auto_partitioning_min_partitions_count) * [AUTO_PARTITIONING_MAX_PARTITIONS_COUNT](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#auto_partitioning_max_partitions_count) * [READ_REPLICAS_SETTINGS](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#read_only_replicas) * for local bloom indexes (see [Parameters of local bloom indexes](#local-bloom)): * `FALSE_POSITIVE_PROBABILITY` * `NGRAM_SIZE` and `CASE_SENSITIVE` (only for `bloom_ngram_filter`) * The min_max index does not support `ALTER INDEX`. {% note info %} The `RESET` operation for `ALTER INDEX` is not supported. {% endnote %} * `` - new parameter value. Possible values include: * `ENABLED` or `DISABLED` for the `AUTO_PARTITIONING_BY_SIZE` and `AUTO_PARTITIONING_BY_LOAD` parameters * `"PER_AZ:"` or `"ANY_AZ:"` where `` is the number of replicas for `READ_REPLICAS_SETTINGS` * for other parameters — an integer of type `Uint64` * for `FALSE_POSITIVE_PROBABILITY` — a floating-point number in the range `(0, 1)`; a smaller value usually reduces the number of false positives but increases the index size * for `NGRAM_SIZE` — an integer in the range from `3` to `8` (usually recommended to start with `3`) * for `CASE_SENSITIVE` — `true` or `false` ### Example The code in the following example enables automatic partitioning by load for the index named `title_index` in the table `series`, sets the minimum number of partitions to 5, and starts one replica in each availability zone (AZ) for each 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" ); ``` For local bloom indexes, you can also change their specific parameters, for example: ```yql ALTER TABLE `/Root/Table` ALTER INDEX idx_ngram SET ( ngram_size = 4, false_positive_probability = 0.005, case_sensitive = false ); ``` ## Deleting an index {#drop-index} `DROP INDEX` — deletes the index with the specified name. The code below will delete the index named `title_index`. ```yql ALTER TABLE `series` DROP INDEX `title_index`; ``` You can also delete an index using the [table index](https://ydb.tech/docs/en/reference/ydb-cli/commands/secondary_index.md?version=main#drop) YDB CLI command. ## Renaming a secondary index {#rename-secondary-index} `RENAME INDEX` — renames the index with the specified name. If an index with the new name already exists, an error will be returned. The ability to atomically replace an index under load is supported by the [ydb table index rename](https://ydb.tech/docs/en/reference/ydb-cli/commands/secondary_index.md?version=main#rename) YDB CLI command and specialized YDB SDK methods. This applies to global secondary indexes (hidden index table and `--replace` mode). Local bloom indexes are not applicable to such atomic replacement under load. Example of renaming an index: ```yql ALTER TABLE `series` RENAME INDEX `title_index` TO `title_index_new`; ```