SHOW RANGE FOR ROW

The SHOW RANGE ... FOR ROW shows information about a for a single row in a table or index. This information is useful for verifying how SQL data maps to underlying ranges, and where the replicas for a range are located.

This feature is in and subject to change. To share feedback and/or issues, contact Support.

To show information about the ranges for all data in a table, index, or database, use the statement.

Syntax

SHOW RANGE FROM TABLE <tablename> FOR ROW (value1, value2, ...)
SHOW RANGE FROM INDEX [ <tablename> @ ] <indexname> FOR ROW (value1, value2, ...)

Required privileges

The user must have the SELECT on the target table.

Parameters

Parameter	Description
tablename	The name of the table that contains the row that you want range information about.
indexname	The name of the index that contains the row that you want range information about.
(value1, value2, ...)	The values of the indexed columns of the row that you want range information about, as a tuple. In previous releases, this statement required the values of all columns of a row.

Response

The following fields are returned:

Field	Description
start_key	The start key for the range.
end_key	The end key for the range.
range_id	The range ID.
lease_holder	The node that contains the range’s .
lease_holder_locality	The of the leaseholder.
replicas	The nodes that contain the range .
replica_localities	The 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 .

To follow along, run to start a temporary, in-memory cluster with the movr dataset preloaded:

$ cockroach demo

Show range information for a row in a table

To show information about a row in a table, you must know the values of the columns in the row’s primary key:

> SHOW INDEX FROM vehicles;

  table_name |              index_name               | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------------------------+------------+--------------+-------------+-----------+---------+-----------
  vehicles   | vehicles_pkey                         |   false    |            1 | city             | ASC       |  false  |  false
  vehicles   | vehicles_pkey                         |   false    |            2 | id               | ASC       |  false  |  false
  vehicles   | vehicles_pkey                         |   false    |            3 | type             | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            4 | owner_id         | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            5 | creation_time    | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            6 | status           | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            7 | current_location | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            8 | ext              | N/A       |  true   |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            1 | city             | ASC       |  false  |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            2 | owner_id         | ASC       |  false  |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            3 | id               | ASC       |  false  |   true
(11 rows)

> SELECT city, id FROM vehicles LIMIT 5;

     city     |                  id
--------------+---------------------------------------
  amsterdam   | bbbbbbbb-bbbb-4800-8000-00000000000b
  amsterdam   | aaaaaaaa-aaaa-4800-8000-00000000000a
  boston      | 22222222-2222-4200-8000-000000000002
  boston      | 33333333-3333-4400-8000-000000000003
  los angeles | 99999999-9999-4800-8000-000000000009
(5 rows)

> SHOW RANGE FROM TABLE vehicles FOR ROW (
    'boston',
    '22222222-2222-4200-8000-000000000002'
  );

                            start_key                           |                         end_key                         | range_id | lease_holder | lease_holder_locality | replicas |    replica_localities
----------------------------------------------------------------+---------------------------------------------------------+----------+--------------+-----------------------+----------+---------------------------
  /"boston"/"\"\"\"\"\"\"B\x00\x80\x00\x00\x00\x00\x00\x00\x02" | /"boston"/"333333D\x00\x80\x00\x00\x00\x00\x00\x00\x03" |       57 |            1 | region=us-east1,az=b  | {1}      | {"region=us-east1,az=b"}
(1 row)

Show range information for a row by a secondary index

To show information about a row in a secondary index, you must know the values of the indexed columns:

> SHOW INDEX FROM vehicles;

  table_name |              index_name               | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------------------------+------------+--------------+-------------+-----------+---------+-----------
  vehicles   | vehicles_pkey                         |   false    |            1 | city             | ASC       |  false  |  false
  vehicles   | vehicles_pkey                         |   false    |            2 | id               | ASC       |  false  |  false
  vehicles   | vehicles_pkey                         |   false    |            3 | type             | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            4 | owner_id         | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            5 | creation_time    | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            6 | status           | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            7 | current_location | N/A       |  true   |  false
  vehicles   | vehicles_pkey                         |   false    |            8 | ext              | N/A       |  true   |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            1 | city             | ASC       |  false  |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            2 | owner_id         | ASC       |  false  |  false
  vehicles   | vehicles_auto_index_fk_city_ref_users |    true    |            3 | id               | ASC       |  false  |   true
(11 rows)

> SELECT city, owner_id, id FROM vehicles@vehicles_auto_index_fk_city_ref_users LIMIT 5;

     city     |               owner_id               |                  id
--------------+--------------------------------------+---------------------------------------
  amsterdam   | bd70a3d7-0a3d-4000-8000-000000000025 | bbbbbbbb-bbbb-4800-8000-00000000000b
  amsterdam   | c28f5c28-f5c2-4000-8000-000000000026 | aaaaaaaa-aaaa-4800-8000-00000000000a
  boston      | 2e147ae1-47ae-4400-8000-000000000009 | 22222222-2222-4200-8000-000000000002
  boston      | 33333333-3333-4400-8000-00000000000a | 33333333-3333-4400-8000-000000000003
  los angeles | 9eb851eb-851e-4800-8000-00000000001f | 99999999-9999-4800-8000-000000000009
(5 rows)

> SHOW RANGE FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users FOR ROW (
    'boston',
    '2e147ae1-47ae-4400-8000-000000000009',
    '22222222-2222-4200-8000-000000000002'
  );

  start_key | end_key | range_id | lease_holder | lease_holder_locality | replicas |    replica_localities
------------+---------+----------+--------------+-----------------------+----------+---------------------------
  NULL      | NULL    |       53 |            1 | region=us-east1,az=b  | {1}      | {"region=us-east1,az=b"}

See also