ALTER INDEX

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

The ALTER INDEX statement changes the definition of an index. For information on using ALTER INDEX, see the pages for its subcommands.

Note:

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.

icon/buttons/copy
$ cockroach demo --geo-partitioned-replicas

Rename an index

icon/buttons/copy
> 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)
icon/buttons/copy
> ALTER INDEX users@name_idx RENAME TO users_name_idx;
icon/buttons/copy
> 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

Tip:

The Cost-based Optimizer can take advantage of replication zones for secondary indexes when optimizing queries.

Note:

This is an enterprise-only feature. Request a 30-day trial license 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).

Tip:

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.

icon/buttons/copy
> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
icon/buttons/copy
> 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.


Yes No
On this page

Yes No