Note
This view is introduced in V1.4.
Purpose
The information_schema.PARTITIONS view displays information about partitions.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_CATALOG | text | NO | Fixed value (def). |
| TABLE_SCHEMA | varchar(128) | NO | The name of the database. |
| TABLE_NAME | varchar(256) | NO | The name of the table. |
| PARTITION_NAME | varchar(64) | NO | The name of the partition. |
| SUBPARTITION_NAME | varchar(64) | NO | The name of the subpartition. |
| PARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the current partition in all partitions. |
| SUBPARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the current subpartition in all subpartitions. |
| PARTITION_METHOD | varchar(13) | NO | The partitioning method. |
| SUBPARTITION_METHOD | varchar(13) | NO | The subpartitioning method. |
| PARTITION_EXPRESSION | text | NO | The partitioning function expression. |
| SUBPARTITION_EXPRESSION | text | NO | The subpartitioning function expression. |
| PARTITION_DESCRIPTION | text | NO | The description for RANGE and LIST partitions. |
| SUBPARTITION_DESCRIPTION | text | NO | The description for RANGE and LIST subpartitions. |
| TABLE_ROWS | bigint(20) unsigned | NO | The number of rows in the partition. |
| AVG_ROW_LENGTH | bigint(21) unsigned | NO | The average length of rows stored in a partition or subpartition. |
| DATA_LENGTH | bigint(0) unsigned | NO | The total number of bytes stored in a partition or subpartition, which indicates the storage space occupied by the table partition or subpartition in units of macroblocks.
NoteIn V4.3.x, this field no longer displays as |
| MAX_DATA_LENGTH | bigint(0) unsigned | NO | Currently not supported. The default value is 0. |
| INDEX_LENGTH | bigint(0) unsigned | NO | The total length of indexes, which indicates the storage space occupied by the indexes of a table partition or subpartition in units of macroblocks.
NoteIn V4.3.x, this field no longer displays as |
| DATA_FREE | bigint(0) unsigned | NO | Currently not supported. The default value is 0. |
| CREATE_TIME | timestamp(6) | NO | The creation time of the partition. |
| UPDATE_TIME | datetime | NO | Currently not supported. The default value is NULL. |
| CHECK_TIME | datetime | YES | Currently not supported. The default value is NULL. |
| CHECKSUM | bigint(0) | NO | Currently not supported. The default value is NULL. |
| PARTITION_COMMENT | text | NO | The comment on the partition. |
| NODEGROUP | varchar(256) | NO | The node group to which the partition belongs. |
| TABLESPACE_NAME | varchar(268) | NO | The name of the tablespace to which the partition belongs. |
Sample query
Query the partitions of the t1 table.
obclient> select * from information_schema.partitions where table_name = 't1' limit 1\G
The query result is as follows:
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t1
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: a
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
SUBPARTITION_DESCRIPTION: NULL
TABLE_ROWS: 3000
AVG_ROW_LENGTH: 37
DATA_LENGTH: 0
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: NULL
CREATE_TIME: 2024-12-17 10:56:36.834215
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
1 row in set (0.020 sec)