On this page 
Warning:
As of November 12, 2021, CockroachDB v20.1 is no longer supported. For more details, refer to the Release Support Policy.
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.
Note:
To show range information for a specific row in a table or index, use the SHOW RANGE ... FOR ROW statement.
Synopsis
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. |
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
Show ranges for a table (primary index)
> 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
> 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
> 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
SHOW RANGE ... FOR ROWSPLIT ATCREATE TABLECREATE INDEX- Indexes
- Partitioning tables
- Architecture Overview
Was this helpful?
