Note
This view is introduced since OceanBase Database 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 partition in all partitions. |
| SUBPARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the 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 expression of the partitioning function. |
| SUBPARTITION_EXPRESSION | text | NO | The expression of the subpartitioning function. |
| PARTITION_DESCRIPTION | text | NO | The description of the partition for RANGE and LIST partitions. |
| SUBPARTITION_DESCRIPTION | text | NO | The description of the subpartition 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 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 table partition or subpartition, calculated in macroblocks. The unit is byte.
NoteIn V4.3.x, this field is effective starting from V4.3.5. |
| MAX_DATA_LENGTH | bigint(0) unsigned | NO | Not supported. The default value is 0. |
| INDEX_LENGTH | bigint(0) unsigned | NO | The total length of indexes, which is the storage space occupied by the indexes of the table partition or subpartition, calculated in macroblocks. The unit is byte.
NoteIn V4.3.x, this field is effective starting from V4.3.5. |
| DATA_FREE | bigint(0) unsigned | NO | Not supported. The default value is 0. |
| CREATE_TIME | timestamp(6) | NO | The creation time of the partition. |
| UPDATE_TIME | datetime | NO | Not supported. The default value is NULL. |
| CHECK_TIME | datetime | YES | Not supported. The default value is NULL. |
| CHECKSUM | bigint(0) | NO | Not supported. The default value is NULL. |
| PARTITION_COMMENT | text | NO | The comment of 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 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: 69632
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 245760
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)