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. |
VISIBLE |
Make an index visible to the cost-based optimizer. |
NOT VISIBLE |
Make an index not visible to the cost-based optimizer. |
Index visibility
Use the VISIBLE and NOT VISIBLE subcommands to set the visibility of an index. This determines whether the index is visible to the cost-based optimizer.
By default, indexes are visible. If NOT VISIBLE, the index will not be used in queries unless it is specifically selected with an index hint or the property is overridden with the optimizer_use_not_visible_indexes session variable.
This allows you to create an index and check for query plan changes without affecting production queries. For an example, see Set an index to be not visible.
Index visibility considerations
- Primary indexes must be visible.
- Indexes that are not visible are still used to enforce
UNIQUEandFOREIGN KEYconstraints. - Indexes that are not visible are still used for foreign key cascades.
- When defining a unique constraint, the
NOT VISIBLEsyntax cannot be used to make the corresponding index not visible. Instead, useALTER INDEXafter creating the unique constraint.
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 Geo-Partitioned Replicas Topology applied to the movr database.
$ cockroach demo --geo-partitioned-replicas
Rename an index
> CREATE INDEX on users(name);
> SHOW INDEXES FROM users;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+------------+------------+--------------+-------------+-----------+---------+----------+----------
users | name_idx | t | 1 | name | DESC | f | f | t
users | name_idx | t | 2 | city | ASC | f | t | t
users | name_idx | t | 3 | id | ASC | f | t | t
users | users_pkey | f | 1 | city | ASC | f | f | t
users | users_pkey | f | 2 | id | ASC | f | f | t
users | users_pkey | f | 3 | name | N/A | t | f | t
users | users_pkey | f | 4 | address | N/A | t | f | t
users | users_pkey | f | 5 | credit_card | N/A | t | f | t
(8 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 | visible
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+----------
users | users_name_idx | t | 1 | name | DESC | f | f | t
users | users_name_idx | t | 2 | city | ASC | f | t | t
users | users_name_idx | t | 3 | id | ASC | f | t | t
users | users_pkey | f | 1 | city | ASC | f | f | t
users | users_pkey | f | 2 | id | ASC | f | f | t
users | users_pkey | f | 3 | name | N/A | t | f | t
users | users_pkey | f | 4 | address | N/A | t | f | t
users | users_pkey | f | 5 | credit_card | N/A | t | f | t
(8 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. 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).
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.
Set an index to be not visible
Start the MovR database on a 3-node CockroachDB demo cluster with a larger data set.
cockroach demo movr --num-histories 250000 --num-promo-codes 250000 --num-rides 125000 --num-users 12500 --num-vehicles 3750 --nodes 3
Show the indexes on the
ridestable. In the last column,visible, you can see that all indexes have the valuet(true).> SHOW INDEXES FROM rides;table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+---------- rides | rides_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 2 | rider_id | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 1 | vehicle_city | ASC | f | f | t ... rides | rides_pkey | f | 10 | revenue | N/A | t | f | t (17 rows)Explain a query that filters on revenue. Since there is no index on the
revenuecolumn, the query performs a full scan.EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;info --------------------------------------------------------------------------------------------------------------------------------------------------- distribution: full vectorized: true • sort │ estimated row count: 12,417 │ order: +revenue │ └── • filter │ estimated row count: 12,417 │ filter: revenue > 90 │ └── • scan estimated row count: 125,000 (100% of the table; stats collected 4 minutes ago) table: rides@rides_pkey spans: FULL SCAN index recommendations: 1 1. type: index creation SQL command: CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time); (19 rows)Create the recommended index.
> CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);Display the indexes on the
ridestable to verify the newly created indexrides_revenue_idx.> SHOW INDEXES FROM rides;table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+---------- rides | rides_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 2 | rider_id | ASC | f | f | t ... rides | rides_revenue_idx | t | 1 | revenue | ASC | f | f | t rides | rides_revenue_idx | t | 2 | vehicle_city | N/A | t | f | t rides | rides_revenue_idx | t | 3 | rider_id | N/A | t | f | t rides | rides_revenue_idx | t | 4 | vehicle_id | N/A | t | f | t rides | rides_revenue_idx | t | 5 | start_address | N/A | t | f | t rides | rides_revenue_idx | t | 6 | end_address | N/A | t | f | t rides | rides_revenue_idx | t | 7 | start_time | N/A | t | f | t rides | rides_revenue_idx | t | 8 | end_time | N/A | t | f | t rides | rides_revenue_idx | t | 9 | city | ASC | f | t | t rides | rides_revenue_idx | t | 10 | id | ASC | f | t | t (27 rows)Explain the query behavior after creating the index. The query now uses the
rides_revenue_idxindex and scans many fewer rows.EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;info ------------------------------------------------------------------------------------- distribution: local vectorized: true • scan estimated row count: 11,600 (9.3% of the table; stats collected 38 seconds ago) table: rides@rides_revenue_idx spans: (/90 - ] (7 rows)Alter the index to be not visible to the optimizer, specifying the
NOT VISIBLEclause.> ALTER INDEX rides_revenue_idx NOT VISIBLE;Display the table indexes and verify that the index visibility for
rides_revenue_idxisf(false).> SHOW INDEXES FROM rides;table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+---------- rides | rides_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 2 | rider_id | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t ... rides | rides_revenue_idx | t | 1 | revenue | ASC | f | f | f rides | rides_revenue_idx | t | 2 | vehicle_city | N/A | t | f | f rides | rides_revenue_idx | t | 3 | rider_id | N/A | t | f | f rides | rides_revenue_idx | t | 4 | vehicle_id | N/A | t | f | f rides | rides_revenue_idx | t | 5 | start_address | N/A | t | f | f rides | rides_revenue_idx | t | 6 | end_address | N/A | t | f | f rides | rides_revenue_idx | t | 7 | start_time | N/A | t | f | f rides | rides_revenue_idx | t | 8 | end_time | N/A | t | f | f rides | rides_revenue_idx | t | 9 | city | ASC | f | t | f rides | rides_revenue_idx | t | 10 | id | ASC | f | t | f (27 rows)Explain the query behavior after making the index not visible to the optimizer. With the index not visible, the optimizer reverts to full scan and recommends that you make the index visible.
EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;info -------------------------------------------------------------------------------------------------------------------------------------- distribution: full vectorized: true • sort │ estimated row count: 12,655 │ order: +revenue │ └── • filter │ estimated row count: 12,655 │ filter: revenue > 90 │ └── • scan estimated row count: 125,000 (100% of the table; stats collected 4 minutes ago; using stats forecast for 10 seconds ago) table: rides@rides_pkey spans: FULL SCAN index recommendations: 1 1. type: index alteration SQL command: ALTER INDEX rides@rides_revenue_idx VISIBLE; (19 rows)
See also
Was this helpful?
