Skip to main content
Version: 3.2

SHOW PARTITIONS

Description

Displays partition information, including common partitions and temporary partitions.

Syntax

SHOW [TEMPORARY] PARTITIONS FROM [db_name.]table_name [WHERE] [ORDER BY] [LIMIT]

NOTE

This syntax only supports StarRocks tables ("ENGINE" = "OLAP"). Since v3.0, this operation requires the SELECT privilege on the specified table. For v2.5 and earlier versions, this operation requires the SELECT__PRIV privilege on the specified table.

Description of return fields

+-------------+---------------+----------------+---------------------+--------------------+--------+--------------+-------+--------------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | VisibleVersionHash | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | LastConsistencyCheckTime | DataSize | IsInMemory | RowCount |
+-------------+---------------+----------------+---------------------+--------------------+--------+--------------+-------+--------------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
FieldDescription
PartitionIdThe ID of the partition.
PartitionNameThe name of the partition.
VisibleVersionThe version number of the last successful load transaction. The version number increases by 1 with each successful load transaction.
VisibleVersionTimeThe timestamp of the last successful load transaction.
VisibleVersionHashThe hash value for the version number of the last successful load transaction.
StateThe status of the partition. Fixed value: Normal.
PartitionKeyThe partition key that consists of one or more partition columns.
RangeThe range of the partition, which is a right half-open interval.
DistributionKeyThe bucket key of hash bucketing.
BucketsThe number of buckets for the partition.
ReplicationNumThe number of replicas per tablet in the partition.
StorageMediumThe storage medium to store the data in the partition. The value HHD indicates hard disk drives, and the value SSD indicates solid-state drives.
CooldownTimeThe cooldown time for data in the partition. If the initial storage medium is SSD, the storage medium is switched from SSD to HDD after the time specified by this parameter. Format: "yyyy-MM-dd HH:mm:ss".
LastConsistencyCheckTimeThe time of the last consistency check. NULL indicates no consistency check was performed.
DataSizeThe size of data in the partition.
IsInMemoryWhether all data in the partition is stored in memory.
RowCountThe number of data rows of the partition.
MaxCSThe maximum Compaction Score of the partition. For shared-data clusters only.

Examples

  1. Display information of all regular partitions from the specified table site_access under the specified database test.

    MySQL > show partitions from test.site_access\G
    *************************** 1. row ***************************
    PartitionId: 20990
    PartitionName: p2019
    VisibleVersion: 1
    VisibleVersionTime: 2023-08-08 15:45:13
    VisibleVersionHash: 0
    State: NORMAL
    PartitionKey: datekey
    Range: [types: [DATE]; keys: [2019-01-01]; ..types: [DATE]; keys: [2020-01-01]; )
    DistributionKey: site_id
    Buckets: 6
    ReplicationNum: 3
    StorageMedium: HDD
    CooldownTime: 9999-12-31 23:59:59
    LastConsistencyCheckTime: NULL
    DataSize: 4KB
    IsInMemory: false
    RowCount: 3
    1 row in set (0.00 sec)
  2. Display information of all temporary partitions from the specified table site_access under the specified database test.

    SHOW TEMPORARY PARTITIONS FROM test.site_access;
  3. Display the information of the specified partition p1 of the specified table site_access under the specified database test.

    -- Regular partition
    SHOW PARTITIONS FROM test.site_access WHERE PartitionName = "p1";
    -- Temporary partition
    SHOW TEMPORARY PARTITIONS FROM test.site_access WHERE PartitionName = "p1";
  4. Display the latest partition information of the specified table site_access under the specified database test.

    -- Regular partition
    SHOW PARTITIONS FROM test.site_access ORDER BY PartitionId DESC LIMIT 1;
    -- Temporary partition
    SHOW TEMPORARY PARTITIONS FROM test.site_access ORDER BY PartitionId DESC LIMIT 1;