SHOW EXPERIMENTAL_RANGES

2020-02-16

The SHOW EXPERIMENTAL_RANGES statement shows information about the ranges that make up a specific table's data, including:

  • The start and end keys for the range(s)
  • The range ID(s)
  • Which nodes contain the range replicas
  • Which node contains the range that is the leaseholder

This information is useful for verifying that:

{{site.data.alerts.callout_danger}} This is an experimental feature. The interface and output are subject to change. {{site.data.alerts.end}}

Synopsis

{% include {{ page.version.version }}/sql/diagrams/show_ranges.html %}

Required privileges

The user must have the SELECT privilege on the target table.

Parameters

Parameter Description
table_name The name of the table you want range information about.
table_name_with_index The name of the index you want range information about.

Examples

The examples in this section operate on a hypothetical "user credit information" table filled with dummy data, running on a 5-node cluster.

> CREATE TABLE credit_users (
       id INT PRIMARY KEY,
       area_code INTEGER NOT NULL,
       name STRING UNIQUE NOT NULL,
       address STRING NOT NULL,
       zip_code INTEGER NOT NULL,
       credit_score INTEGER NOT NULL
);

We added a secondary index to the table on the area_code column:

> CREATE INDEX areaCode on credit_users(area_code);

Next, we ran a couple of SPLIT ATs on the table and the index:

> ALTER TABLE credit_users SPLIT AT VALUES (5), (10), (15);

> ALTER INDEX credit_users@areaCode SPLIT AT VALUES (400), (600), (999);

{{site.data.alerts.callout_info}} In the example output below, a NULL in the Start Key column means "beginning of table".
A NULL in the End Key column means "end of table". {{site.data.alerts.end}}

Show ranges for a table (primary index)

> SHOW EXPERIMENTAL_RANGES FROM TABLE credit_users;

+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /5      |      158 | {2,3,5}  |            5 |
| /5        | /10     |      159 | {3,4,5}  |            5 |
| /10       | /15     |      160 | {2,4,5}  |            5 |
| /15       | NULL    |      161 | {2,3,5}  |            5 |
+-----------+---------+----------+----------+--------------+
(4 rows)

Show ranges for an index

> SHOW EXPERIMENTAL_RANGES FROM INDEX credit_users@areaCode;

+-----------+---------+----------+-----------+--------------+
| start_key | end_key | range_id | replicas  | lease_holder |
+-----------+---------+----------+-----------+--------------+
| NULL      | /400    |      135 | {2,4,5}   |            2 |
| /400      | /600    |      136 | {2,4,5}   |            4 |
| /600      | /999    |      137 | {1,3,4,5} |            3 |
| /999      | NULL    |       72 | {2,3,4,5} |            4 |
+-----------+---------+----------+-----------+--------------+
(4 rows)

See also