# UNSPLIT AT

The `UNSPLIT AT` statement removes a split enforcement on a range split, at a specified row in a table or index.

Removing a split enforcement from a table or index ("unsplitting") allows CockroachDB to merge ranges as needed, to help improve your cluster's performance. For more information, see Range Merges.

## Synopsis

## Required privileges

The user must have the `INSERT` privilege on the table or index.

## Parameters

Parameter | Description
--- | ---
`table_name` | The name of the table that you want to unsplit.
`table_name @ index_name` `standalone_index_name` | The name of the index that you want to unsplit.
`select_stmt` | A selection query that produces one or more rows at which to unsplit a table or index.

## 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 `--nodes` and `--demo-locality` flags. This command opens an interactive SQL shell to a temporary, multi-node in-memory cluster with the `movr` database preloaded and set as the current database.

    $ cockroach demo --nodes=3 --demo-locality=region=us-east1:region=us-central1:region=us-west1

### Unsplit a table

The `crdb_internal.ranges` table contains information about ranges in your CockroachDB cluster. At this point, just one range contains the data in the `users` table:

    > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='users';

      range_id | start_pretty | end_pretty | split_enforced_until
    +----------+--------------+------------+----------------------+
            21 | /Table/53    | /Table/54  | NULL
    (1 row)

Now split the `users` table ranges based on primary key values:

    > ALTER TABLE users SPLIT AT VALUES ('chicago'), ('new york'), ('seattle');

                  key              |         pretty         |       split_enforced_until
    +------------------------------+------------------------+----------------------------------+
      \275\211\022chicago\000\001  | /Table/53/1/"chicago"  | 2262-04-11 23:47:16.854776+00:00
      \275\211\022new york\000\001 | /Table/53/1/"new york" | 2262-04-11 23:47:16.854776+00:00
      \275\211\022seattle\000\001  | /Table/53/1/"seattle"  | 2262-04-11 23:47:16.854776+00:00
    (3 rows)

You can see the additional ranges in the `crdb_internal.ranges` table:

    > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='users';

      range_id |      start_pretty      |       end_pretty       |       split_enforced_until
    +----------+------------------------+------------------------+----------------------------------+
            21 | /Table/53              | /Table/53/1/"chicago"  | NULL
            27 | /Table/53/1/"chicago"  | /Table/53/1/"new york" | 2262-04-11 23:47:16.854776+00:00
            28 | /Table/53/1/"new york" | /Table/53/1/"seattle"  | 2262-04-11 23:47:16.854776+00:00
            29 | /Table/53/1/"seattle"  | /Table/54              | 2262-04-11 23:47:16.854776+00:00
    (4 rows)

Now unsplit the table to remove the split enforcements:

    > ALTER TABLE users UNSPLIT AT VALUES ('chicago'), ('new york'), ('seattle');

                  key              |         pretty
    +------------------------------+------------------------+
      \275\211\022chicago\000\001  | /Table/53/1/"chicago"
      \275\211\022new york\000\001 | /Table/53/1/"new york"
      \275\211\022seattle\000\001  | /Table/53/1/"seattle"
    (3 rows)

    > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='users';

      range_id |      start_pretty      |       end_pretty       | split_enforced_until
    +----------+------------------------+------------------------+----------------------+
            21 | /Table/53              | /Table/53/1/"chicago"  | NULL
            27 | /Table/53/1/"chicago"  | /Table/53/1/"new york" | NULL
            28 | /Table/53/1/"new york" | /Table/53/1/"seattle"  | NULL
            29 | /Table/53/1/"seattle"  | /Table/54              | NULL
    (4 rows)

The `users` table is still split into ranges at `'chicago'`, `'new york'`, and `'seattle'`, but the `split_enforced_until` column is now `NULL` for all ranges in the table. The split is no longer enforced, and CockroachDB can merge the data in the table as needed.

### Unsplit an index

Add a new secondary index to the `rides` table, on the `revenue` column, and then split the table ranges by secondary index values:

    > CREATE INDEX revenue_idx ON rides(revenue);

    > ALTER INDEX rides@revenue_idx SPLIT AT VALUES (25.00), (50.00), (75.00);

             key        |      pretty      |       split_enforced_until
    +-------------------+------------------+----------------------------------+
      \277\214*2\000    | /Table/55/4/25   | 2262-04-11 23:47:16.854776+00:00
      \277\214*d\000    | /Table/55/4/5E+1 | 2262-04-11 23:47:16.854776+00:00
      \277\214*\226\000 | /Table/55/4/75   | 2262-04-11 23:47:16.854776+00:00
    (3 rows)

    > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='rides';

      range_id |   start_pretty   |    end_pretty    |       split_enforced_until
    +----------+------------------+------------------+----------------------------------+
            23 | /Table/55        | /Table/55/4      | NULL
            32 | /Table/55/4      | /Table/55/4/25   | 2019-09-10 21:27:35.056275+00:00
            33 | /Table/55/4/25   | /Table/55/4/5E+1 | 2262-04-11 23:47:16.854776+00:00
            34 | /Table/55/4/5E+1 | /Table/55/4/75   | 2262-04-11 23:47:16.854776+00:00
            35 | /Table/55/4/75   | /Table/56        | 2262-04-11 23:47:16.854776+00:00
    (5 rows)

Now unsplit the index to remove the split enforcements:

    > ALTER INDEX rides@revenue_idx UNSPLIT AT VALUES (25.00), (50.00), (75.00);

             key        |      pretty
    +-------------------+------------------+
      \277\214*2\000    | /Table/55/4/25
      \277\214*d\000    | /Table/55/4/5E+1
      \277\214*\226\000 | /Table/55/4/75
    (3 rows)

    > SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='rides';

      range_id |   start_pretty   |    end_pretty    |       split_enforced_until
    +----------+------------------+------------------+----------------------------------+
            23 | /Table/55        | /Table/55/4      | NULL
            32 | /Table/55/4      | /Table/55/4/25   | 2019-09-10 21:27:35.056275+00:00
            33 | /Table/55/4/25   | /Table/55/4/5E+1 | NULL
            34 | /Table/55/4/5E+1 | /Table/55/4/75   | NULL
            35 | /Table/55/4/75   | /Table/56        | NULL
    (5 rows)

The table is still split into ranges at `25.00`, `50.00`, and `75.00`, but the `split_enforced_until` column is now `NULL` for all ranges in the table. The split is no longer enforced, and CockroachDB can merge the data in the table as needed.

## See also

- `SPLIT AT`
- Selection Queries
- Distribution Layer
- Range Merges
- Replication Layer
- `SHOW JOBS`