SPLIT AT

The SPLIT AT statement forces a key-value layer range split at the specified row in a table or index.

Synopsis

ALTERTABLEtable_nameSPLITATselect_stmt ALTERINDEXtable_name@index_nameSPLITATselect_stmt

Required privileges

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

Parameters

ParameterDescription
table_nametable_name @ index_nameThe name of the table or index that should be split.
select_stmtA selection query that produces one or more rows at which to split the table or index.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Why manually split a range?

The key-value layer of CockroachDB is broken into sections of contiguouskey-space known as ranges. By default, CockroachDB attempts to keep ranges belowa size of 64MiB. To do this, the system will automatically splita range if it grows larger than this limit. For most use cases, this automaticrange splitting is sufficient, and you should never need to worry aboutwhen or where the system decides to split ranges.

However, there are reasons why you may want to perform manual splits onthe ranges that store tables or indexes:

  • When a table only consists of a single range, all writes and reads to thetable will be served by that range's leaseholder.If a table only holds a small amount of data but is serving a large amount of traffic,load distribution can become unbalanced. Splitting the table's ranges manuallycan allow the load on the table to be more evenly distributed across multiplenodes. For tables consisting of more than a few ranges, load will naturallybe distributed across multiple nodes and this will not be a concern.

  • When a table is created, it will only consist of a single range. If you knowthat a new table will immediately receive significant writetraffic, you may want to preemptively split the table based on the expecteddistribution of writes before applying the load. This can help avoid reducedworkload performance that results when automatic splits are unable to keep upwith write traffic.

Note that when a table is truncated, it is essentially re-created in a single new empty range, and the old ranges that used to constitute the table are garbage collected. Any pre-splitting you have performed on the old version of the table will not carry over to the new version. The new table will need to be pre-split again.

Examples

Split a table

  1. > SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | NULL    |       72 | {1}      |            1 |
  5. +-----------+---------+----------+----------+--------------+
  6. (1 row)
  1. > ALTER TABLE kv SPLIT AT VALUES (10), (20), (30);
  1. +------------+----------------+
  2. |    key     |     pretty     |
  3. +------------+----------------+
  4. | \u0209\x92 | /Table/64/1/10 |
  5. | \u0209\x9c | /Table/64/1/20 |
  6. | \u0209\xa6 | /Table/64/1/30 |
  7. +------------+----------------+
  8. (3 rows)
  1. > SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | /10     |       72 | {1}      |            1 |
  5. | /10       | /20     |       73 | {1}      |            1 |
  6. | /20       | /30     |       74 | {1}      |            1 |
  7. | /30       | NULL    |       75 | {1}      |            1 |
  8. +-----------+---------+----------+----------+--------------+
  9. (4 rows)

Split a table with a composite primary key

You may want to split a table with a composite primary key (e.g., when working with partitions).

Given the table

  1. CREATE TABLE t (k1 INT, k2 INT, v INT, w INT, PRIMARY KEY (k1, k2));

we can split it at its primary key like so:

  1. ALTER TABLE t SPLIT AT VALUES (5,1), (5,2), (5,3);
  1. +------------+-----------------+
  2. |    key     |     pretty      |
  3. +------------+-----------------+
  4. | \xbc898d89 | /Table/52/1/5/1 |
  5. | \xbc898d8a | /Table/52/1/5/2 |
  6. | \xbc898d8b | /Table/52/1/5/3 |
  7. +------------+-----------------+
  8. (3 rows)

To see more information about the range splits, run:

  1. SHOW EXPERIMENTAL_RANGES FROM TABLE t;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | /5/1    |      151 | {2,3,5}  |            5 |
  5. | /5/1      | /5/2    |      152 | {2,3,5}  |            5 |
  6. | /5/2      | /5/3    |      153 | {2,3,5}  |            5 |
  7. | /5/3      | NULL    |      154 | {2,3,5}  |            5 |
  8. +-----------+---------+----------+----------+--------------+
  9. (4 rows)

Alternatively, you could split at a prefix of the primary key columns. For example, to add a split before all keys that start with 3, run:

  1. > ALTER TABLE t SPLIT AT VALUES (3);
  1. +----------+---------------+
  2. |   key    |    pretty     |
  3. +----------+---------------+
  4. | \xcd898b | /Table/69/1/3 |
  5. +----------+---------------+
  6. (1 row)

Conceptually, this means that the second range will include keys that start with 3 through ∞:

  1. SHOW EXPERIMENTAL_RANGES FROM TABLE t;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | /3      |      155 | {2,3,5}  |            5 |
  5. | /3        | NULL    |      165 | {2,3,5}  |            5 |
  6. +-----------+---------+----------+----------+--------------+
  7. (2 rows)

Split an index

  1. > CREATE INDEX secondary ON kv (v);
  1. > SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | NULL    |       75 | {1}      |            1 |
  5. +-----------+---------+----------+----------+--------------+
  6. (1 row)
  1. > ALTER INDEX kv@secondary SPLIT AT (SELECT v FROM kv LIMIT 3);
  1. +---------------------+-----------------+
  2. |         key         |     pretty      |
  3. +---------------------+-----------------+
  4. | \u020b\x12a\x00\x01 | /Table/64/3/"a" |
  5. | \u020b\x12b\x00\x01 | /Table/64/3/"b" |
  6. | \u020b\x12c\x00\x01 | /Table/64/3/"c" |
  7. +---------------------+-----------------+
  8. (3 rows)
  1. > SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
  1. +-----------+---------+----------+----------+--------------+
  2. | start_key | end_key | range_id | replicas | lease_holder |
  3. +-----------+---------+----------+----------+--------------+
  4. | NULL      | /"a"    |       75 | {1}      |            1 |
  5. | /"a"      | /"b"    |       76 | {1}      |            1 |
  6. | /"b"      | /"c"    |       77 | {1}      |            1 |
  7. | /"c"      | NULL    |       78 | {1}      |            1 |
  8. +-----------+---------+----------+----------+--------------+
  9. (4 rows)

See also

Was this page helpful?
YesNo