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.