ALTER INDEX

On this page Carat arrow pointing down

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.
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 UNIQUE and FOREIGN KEY constraints.
  • Indexes that are not visible are still used for foreign key cascades.
  • When defining a unique constraint, the NOT VISIBLE syntax cannot be used to make the corresponding index not visible. Instead, use ALTER INDEX after 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 movr database.

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

Rename an index

icon/buttons/copy
> CREATE INDEX on users(name);
icon/buttons/copy
> 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)
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 | 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

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.

Set an index to be not visible

Start the MovR database on a 3-node CockroachDB demo cluster with a larger data set.

icon/buttons/copy
cockroach demo movr --num-histories 250000 --num-promo-codes 250000 --num-rides 125000 --num-users 12500 --num-vehicles 3750 --nodes 3
  1. Show the indexes on the rides table. In the last column, visible, you can see that all indexes have the value t (true).

    icon/buttons/copy
    > 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)
    
  2. Explain a query that filters on revenue. Since there is no index on the revenue column, the query performs a full scan.

    icon/buttons/copy
    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)
    
  3. Create the recommended index.

    icon/buttons/copy
    > CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);
    
  4. Display the indexes on the rides table to verify the newly created index rides_revenue_idx.

    icon/buttons/copy
    > 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)
    
  5. Explain the query behavior after creating the index. The query now uses the rides_revenue_idx index and scans many fewer rows.

    icon/buttons/copy
    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)
    
  6. Alter the index to be not visible to the optimizer, specifying the NOT VISIBLE clause.

    icon/buttons/copy
    > ALTER INDEX rides_revenue_idx NOT VISIBLE;
    
  7. Display the table indexes and verify that the index visibility for rides_revenue_idx is f (false).

    icon/buttons/copy
    > 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)
    
  8. 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.

    icon/buttons/copy
    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


Yes No
On this page

Yes No