The SHOW RANGE ... FOR ROW statement shows information about a range 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.

Warning:

This is an experimental feature. The interface and output are subject to change.

Note:

To show information about the ranges for all data in a table, index, or database, use the SHOW RANGES 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 privilege 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, ...) New in v20.2: 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 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 to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ 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:

copy
icon/buttons/copy
> SHOW INDEX FROM vehicles;
  table_name |              index_name               | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------------------------+------------+--------------+-------------+-----------+---------+-----------
  vehicles   | primary                               |   false    |            1 | city        | ASC       |  false  |  false
  vehicles   | primary                               |   false    |            2 | id          | ASC       |  false  |  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
(5 rows)
copy
icon/buttons/copy
> 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)
copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> SHOW INDEX FROM vehicles;
  table_name |              index_name               | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------------------------+------------+--------------+-------------+-----------+---------+-----------
  vehicles   | primary                               |   false    |            1 | city        | ASC       |  false  |  false
  vehicles   | primary                               |   false    |            2 | id          | ASC       |  false  |  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
(5 rows)
copy
icon/buttons/copy
> 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)
copy
icon/buttons/copy
> 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



Yes No