Note
This view was introduced in V1.4.
Purpose
The information_schema.PARTITIONS view displays partition information.
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 among all partitions. |
| SUBPARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the current subpartition among all subpartitions. |
| PARTITION_METHOD | varchar(13) | NO | The partitioning type. |
| SUBPARTITION_METHOD | varchar(13) | NO | The subpartitioning type. |
| PARTITION_EXPRESSION | text | NO | The partitioning function expression. |
| SUBPARTITION_EXPRESSION | text | NO | The subpartitioning function expression. |
| PARTITION_DESCRIPTION | text | NO | The description of the RANGE and LIST partitions. |
| SUBPARTITION_DESCRIPTION | text | NO | The description of the 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 the partition or subpartition. |
| DATA_LENGTH | bigint(0) unsigned | NO | The total number of bytes stored in the partition or subpartition, which is the storage space occupied by the partition or subpartition in macroblocks. Unit: Byte.
NoteFor OceanBase Database V4.3.x, this field is no longer displayed as |
| MAX_DATA_LENGTH | bigint(0) unsigned | NO | This field is not supported at present. The default value of this field is 0. |
| INDEX_LENGTH | bigint(0) unsigned | NO | The total length of the index, which is the storage space occupied by the index on the partition or subpartition in macroblocks. Unit: Byte.
NoteFor OceanBase Database V4.3.x, this field is no longer displayed as |
| DATA_FREE | bigint(0) unsigned | NO | This field is not supported at present. The default value of this field is 0. |
| CREATE_TIME | timestamp(6) | NO | The creation time of the partition. |
| UPDATE_TIME | datetime | NO | This field is not supported at present. The default value of this field is NULL. |
| CHECK_TIME | datetime | YES | This field is not supported at present. The default value of this field is NULL. |
| CHECKSUM | bigint(0) | NO | This field is not supported at present. The default value of this field is NULL. |
| PARTITION_COMMENT | text | NO | The comment on the partition. |
| NODEGROUP | varchar(256) | NO | The nodegroup to which the partition belongs. |
| TABLESPACE_NAME | varchar(268) | NO | The name of the tablespace to which the partition belongs. |
Sample query
Query the partition information from 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)