Adding, removing, and renaming a secondary index
Warning
Supported only for row-oriented tables. Support for column-oriented tables is currently under development.
Adding an index
ADD INDEX
: Adds an index with the specified name and type for a given set of columns. The code below adds a global index named title_index
for the title
column.
ALTER TABLE `series` ADD INDEX `title_index` GLOBAL ON (`title`);
You can specify any index parameters from the CREATE TABLE
command.
You can also add a secondary index using the YDB CLI table index command.
Altering an index
Indexes have type-specific parameters that can be tuned. Global indexes, whether synchronous or asynchronous, are implemented as hidden tables, and their automatic partitioning settings can be adjusted just like those of regular tables.
Note
Currently, specifying secondary index partitioning settings during index creation is not supported in either the ALTER TABLE ADD INDEX
or the CREATE TABLE INDEX
statements.
ALTER TABLE <table_name> ALTER INDEX <index_name> SET <partitioning_setting_name> <value>;
ALTER TABLE <table_name> ALTER INDEX <index_name> SET (<partitioning_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.<partitioning_setting_name>
: The name of the setting to be modified, which should be one of the following:
Note
These settings cannot be reset.
<value>
: The new value for the setting. Possible values include:ENABLED
orDISABLED
for theAUTO_PARTITIONING_BY_SIZE
andAUTO_PARTITIONING_BY_LOAD
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 table series
and sets its minimum partition count to 5:
ALTER TABLE `series` ALTER INDEX `title_index` SET (
AUTO_PARTITIONING_BY_LOAD = ENABLED,
AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 5
);
Deleting an index
DROP INDEX
: Deletes the index with the specified name. The code below deletes the index named title_index
.
ALTER TABLE `series` DROP INDEX `title_index`;
You can also remove a secondary index using the YDB CLI table index command.
Renaming an 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 in the YDB CLI and by YDB SDK ad-hoc methods.
Example of index renaming:
ALTER TABLE `series` RENAME INDEX `title_index` TO `title_index_new`;