The ALTER INDEX statement changes the definition of an index. For information on using ALTER INDEX, see the pages for its subcommands.
The ALTER INDEX statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Subcommands
| Subcommand | Description |
|---|---|
CONFIGURE ZONE |
Configure replication zones for an index. |
PARTITION BY |
Partition, re-partition, or un-partition an index. (Enterprise-only). |
RENAME TO |
Change the name of an index. |
SPLIT AT |
Force a range split at the specified row in the index. |
UNSPLIT AT |
Remove a range split enforcement in the index. |
View schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.
Examples
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo with the --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the the movr database.
$ cockroach demo --geo-partitioned-replicas
Rename an index
> SHOW INDEXES FROM users;
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| users | primary | false | 1 | id | ASC | false | false |
| users | name_idx | true | 1 | name | ASC | false | false |
| users | name_idx | true | 2 | id | ASC | false | true |
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
(3 rows)
> ALTER INDEX users@name_idx RENAME TO users_name_idx;
> SHOW INDEXES FROM users;
+------------+----------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+----------------+------------+--------------+-------------+-----------+---------+----------+
| users | primary | false | 1 | id | ASC | false | false |
| users | users_name_idx | true | 1 | name | ASC | false | false |
| users | users_name_idx | true | 2 | id | ASC | false | true |
+------------+----------------+------------+--------------+-------------+-----------+---------+----------+
(3 rows)
Create a replication zone for a secondary index
The Cost-based Optimizer can take advantage of replication zones for secondary indexes when optimizing queries.
This is an enterprise-only feature. You can use free trial credits to try it out.
The secondary indexes on a table will automatically use the replication zone for the table. However, with an enterprise license, you can add distinct replication zones for secondary indexes.
To control replication for a specific secondary index, use the ALTER INDEX ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone).
To get the name of a secondary index, which you need for the CONFIGURE ZONE statement, use the SHOW INDEX or SHOW CREATE TABLE statements.
> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
> SHOW ZONE CONFIGURATION FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;
target | raw_config_sql
+------------------------------------------------------+---------------------------------------------------------------------------------+
INDEX vehicles@vehicles_auto_index_fk_city_ref_users | ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 100000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
(1 row)
Split and unsplit an index
For examples, see Split an index and Unsplit an index.
