The SHOW RANGES statement shows information about the ranges that comprise the data for a table, index, or entire database. This information is useful for verifying how SQL data maps to underlying ranges, and where the replicas for ranges are located. If SHOW RANGES displays NULL for both the start and end keys of a range, the range is empty and has no splits.

Note:

To show range information for a specific row in a table or index, use the SHOW RANGE ... FOR ROW statement.

Synopsis

SHOW RANGES FROM TABLE table_name INDEX table_index_name DATABASE database_name

Required privileges

Only members of the admin role can run SHOW RANGES. By default, the root user belongs to the admin role.

Parameters

Parameter Description
table_name The name of the table you want range information about.
table_index_name The name of the index you want range information about.
database_name The name of the database you want range information about.

Response

The following fields are returned for each partition:

Field Description
table_name The name of the table.
start_key The start key for the range.
end_key The end key for the range.
range_id The range ID.
range_size_mb The size of the range.
lease_holder The node that contains the range's leaseholder.
lease_holder_locality The locality of the leaseholder.
replicas The nodes that contain the range replicas.
replica_localities The locality of the range.
Note:

If both start_key and end_key show NULL, the range is empty and has no splits.

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.

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

Show ranges for a table (primary index)

copy
icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM TABLE vehicles] WHERE "start_key" NOT LIKE '%Prefix%';
     start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  /"new york"      | /"new york"/PrefixEnd      |       58 |      0.000304 |            2 | region=us-east1,az=c     | {1,2,5}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-west1,az=b"}
  /"washington dc" | /"washington dc"/PrefixEnd |      102 |      0.000173 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"boston"        | /"boston"/PrefixEnd        |       63 |      0.000288 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"seattle"       | /"seattle"/PrefixEnd       |       97 |      0.000295 |            4 | region=us-west1,az=a     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"los angeles"   | /"los angeles"/PrefixEnd   |       55 |      0.000156 |            5 | region=us-west1,az=b     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"san francisco" | /"san francisco"/PrefixEnd |       71 |      0.000309 |            6 | region=us-west1,az=c     | {1,5,6}  | {"region=us-east1,az=b","region=us-west1,az=b","region=us-west1,az=c"}
  /"amsterdam"     | /"amsterdam"/PrefixEnd     |       59 |      0.000305 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"paris"         | /"paris"/PrefixEnd         |       62 |      0.000299 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"rome"          | /"rome"/PrefixEnd          |       67 |      0.000168 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
(9 rows)

Show ranges for an index

copy
icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM INDEX vehicles_auto_index_fk_city_ref_users] WHERE "start_key" NOT LIKE '%Prefix%';
     start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  /"washington dc" | /"washington dc"/PrefixEnd |      188 |      0.000089 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"boston"        | /"boston"/PrefixEnd        |      141 |      0.000164 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"new york"      | /"new york"/PrefixEnd      |      168 |      0.000174 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"los angeles"   | /"los angeles"/PrefixEnd   |      165 |      0.000087 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"san francisco" | /"san francisco"/PrefixEnd |      174 |      0.000183 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"seattle"       | /"seattle"/PrefixEnd       |      186 |      0.000166 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"amsterdam"     | /"amsterdam"/PrefixEnd     |      137 |       0.00017 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"paris"         | /"paris"/PrefixEnd         |      170 |      0.000162 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"rome"          | /"rome"/PrefixEnd          |      172 |       0.00008 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
(9 rows)

Show ranges for a database

copy
icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM database movr] WHERE "start_key" NOT LIKE '%Prefix%';
          table_name         |    start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+----------------------------+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  users                      | /"amsterdam"     | /"amsterdam"/PrefixEnd     |       47 |      0.000562 |            7 | region=europe-west1,az=b | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  users                      | /"boston"        | /"boston"/PrefixEnd        |       51 |      0.000665 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  users                      | /"chicago"       | /"los angeles"             |       83 |             0 |            4 | region=us-west1,az=a     | {2,4,8}  | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"}
  users                      | /"los angeles"   | /"los angeles"/PrefixEnd   |       45 |      0.000697 |            4 | region=us-west1,az=a     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  users                      | /"new york"      | /"new york"/PrefixEnd      |       48 |      0.000664 |            1 | region=us-east1,az=b     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  users                      | /"paris"         | /"paris"/PrefixEnd         |       52 |      0.000628 |            8 | region=europe-west1,az=c | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}

  ...

  user_promo_codes           | /"washington dc" | /"washington dc"/PrefixEnd |      144 |             0 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
(73 rows)

See also



YesYes NoNo