This topic describes the syntax that is used to manually create a partitioned table, select a partition, and view the partition information.
Precautions
Before you create a partitioned table in a logical database, make sure that the logical database uses the automatic partitioning mode (mode='auto'). If the logical database does not use the automatic partitioning mode, you cannot create a partitioned table. You can execute the
SHOW CREATE DATBASE db_namestatement to query the partitioning mode of the logical database. Example:CREATE DATABASE part_db mode='auto'; Query OK, 1 row affected (4.29 sec) SHOW CREATE DATABASE part_db; +----------+-----------------------------------------------+ | DATABASE | CREATE DATABASE | +----------+-----------------------------------------------+ | part_db | CREATE DATABASE `part_db` /* MODE = 'auto' */ | +----------+-----------------------------------------------+ 1 row in set (0.18 sec)For information about the syntax that is used to create a database, see CREATE DATABASE.
If the primary key of a partitioned table does not contain a partition key and is not an auto-increment primary key, make sure that the primary key is unique.
If features related to level-2 partitions are required when a partitioned table is being created, the version of your PolarDB-X instance is 5.4.17-16952556 or later.
Partitioning methods
Level-1 partitioning
PolarDB-X supports the following methods and policies for level-1 partitioning:
HASH partitioning: This partitioning method uses the built-in consistent hashing to calculate the hash value of a specified partition key column or expression that contains a partitioning function and routes data to partitions. The HASH partitioning method includes KEY partitioning and HASH partitioning based on the hashing routing algorithm and usage method.
RANGE partitioning: This partitioning method compares and calculates the value of a specified partition key column or the value returned by a specified expression that contains a partitioning function to determine the range of predefined partitions in which data is distributed and routes the data to partitions. The RANGE partitioning method includes RANGE COLUMNS partitioning and RANGE partitioning based on whether multiple partition key columns are used as partition keys and the usage method.
LIST partitioning: This partitioning method compares and calculates the value of a specified partition key column or the value returned by a specified expression that contains a partitioning function to determine the value collection of predefined partitions in which data is distributed and routes the data to partitions. This partitioning method is similar to the RANGE partitioning method. The LIST partitioning method includes LIST COLUMNS partitioning and LIST partitioning based on whether multiple partition key columns are used as partition keys and the usage method.
CO_HASH partitioning This partitioning method is designed to efficiently partition a table that has multiple columns with values in a consistent mapping relationship. This method uniformly distributes data across partitions, regardless of whether you use one or more of the mapping columns as the partition key columns.
For more information, see the "Partitioning methods" section of the CREATE TABLE (AUTO mode) topic.
Level-2 partitioning
PolarDB-X supports the following policies for level-2 partitioning: HASH, KEY, RANGE COLUMNS, RANGE, LIST, and LIST COLUMNS. Level-1 and level-2 partitions in PolarDB-X are in a completely orthogonal relationship. You can combine two partitioning policies for level-2 partitioning. Up to 36 combinations are supported.
Templated and non-templated syntax
Templated level-2 partitions: The number of level-2 partitions under each level-1 partition is always consistent with the boundary value of each level-1 partition.
Non-templated level-2 partitions: The number of level-2 partitions under each level-1 partition is always inconsistent with the boundary value of each level-1 partition.
Level-2 partitions support up to 36 types of composite partitions. Each type of composite partitions supports templated and non-templated syntax that is used to create a partition.
Usage notes for level-2 partitions
By default, the total number of level-2 partitions under all level-1 partition in a partitioned table cannot exceed 8,192.
After level-2 partitions are used, the number of level-2 partitions in a partitioned table is the total number of level-2 partitions under all level-1 partitions. Therefore, the number of partitions in the partitioned table increases exponentially. To avoid negative effects caused by excessive partitioning operations or errors caused by the failure to maintain the total number of partitions, we recommend that you carefully control the number of shards in level-1 and level-2 partitions.
Create level-1 partitions
Create HASH level-1 partitions
With a partition function
Use a single-column partition key datetime and adopt the to_days partitioning function to convert the key to the number of days, perform HASH partitioning, and then specify that the number of partitions is 8.
CREATE TABLE tb_h_fn(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;Without a partition function
Use a single-column partition key id to perform HASH partitioning and specify that the number of partitions is 8.
CREATE TABLE tb_h(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(id)
PARTITIONS 8;Use a vector partition key (id, bid) to perform HASH partitioning and specify that the number of partitions is 8.
If id and bid are jointly used in the hash value calculation for partitioning, partition pruning takes effect and queries are correctly routed to the related partitions only when the queries include both the id and bid equality conditions.
CREATE TABLE tb_h2(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(id,bid)
PARTITIONS 8;Create KEY level-1 partitions
Use a vector partition key (id, bid) to perform KEY partitioning to evenly distribute the table data across eight partitions.
By default, when the first column involves the hash value calculation, you need to only prefix the equality conditions for the column in an equality query to trigger partition pruning.
CREATE TABLE tb_k(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(id, bid)
PARTITIONS 8;Create RANGE level-1 partitions
With a partition function
Use a single-column partition key datetime and adopt the to_days partition function to convert the key to the number of days, and then perform RANGE partitioning.
CREATE TABLE tb_r_fn(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2020-01-01')),
PARTITION p2 VALUES LESS THAN(TO_DAYS('2021-01-01')),
PARTITION p3 VALUES LESS THAN(TO_DAYS('2022-01-01')),
PARTITION pm VALUES LESS THAN(MAXVALUE)
)Without a partition function
Use a single-column partition key id to perform RANGE partitioning and specify that the number of partitions is 8.
CREATE TABLE tb_r(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(id)
(
PARTITION p1 VALUES LESS THAN(1000),
PARTITION p2 VALUES LESS THAN(2000),
PARTITION p3 VALUES LESS THAN(3000),
PARTITION pm VALUES LESS THAN(MAXVALUE)
)Create RANGE COLUMNS level-1 partitions
Use a vector partition key (birthday, id) to perform RANGE partitioning.
CREATE TABLE tb_rc(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday, id)
(
PARTITION p1 VALUES LESS THAN('2020-01-01', 1000),
PARTITION p2 VALUES LESS THAN('2021-01-01', 2000),
PARTITION p3 VALUES LESS THAN('2022-01-01', 3000),
PARTITION pm VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Create LIST level-1 partitions
With a partition function
Use a single-column partition key datetime and adopt the to_days partition function to convert the key to the number of days, and then perform LIST partitioning.
CREATE TABLE tb_l_fn(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
(
PARTITION p1 VALUES IN (TO_DAYS('2020-01-01'),TO_DAYS('2020-02-01')),
PARTITION p2 VALUES IN (TO_DAYS('2021-01-01'),TO_DAYS('2021-02-01')),
PARTITION p3 VALUES IN (TO_DAYS('2022-01-01')),
PARTITION pm VALUES IN (DEFAULT)
)Without a partition function
Use a single-column partition key id to perform LIST partitioning and specify that the number of partitions is 8.
CREATE TABLE tb_l(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(id)
(
PARTITION p1 VALUES IN (1000,1001,1002),
PARTITION p2 VALUES IN (2000,2001,2002),
PARTITION p3 VALUES IN (3000),
PARTITION pm VALUES IN (DEFAULT)
)Create LIST COLUMNS level-1 partitions
Use a vector partition key (birthday, id) to perform LIST partitioning.
CREATE TABLE tb_lc(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday, id)
(
PARTITION p1 VALUES IN (('2020-01-01', 1000),('2020-01-01', 2000)),
PARTITION p2 VALUES IN (('2021-01-01', 1000),('2021-01-01', 2000)),
PARTITION p3 VALUES IN (('2022-01-01', 1000),('2022-01-01', 2000)),
PARTITION pm VALUES IN (DEFAULT)
)Create CO_HASH level-1 partitions
If the values of two or more columns in a table consistently map, you can use the CO_HASH partitioning method to partition the table. For example, in a table named orders, if the last N digits of the order_id and buyer_id columns are always identical, you can use the CO_HASH partitioning method to partition the table. To perform queries on a CO_HASH partitioned table, you need to only include an equality condition on one of the mapped columns in the queries.
Differences among CO_HASH(a,b), HASH(a,b), and KEY(a,b)
Relationships between the partition key columns
KEY(a,b): This method uses a hierarchical combination of the partition key columns a and b to partition a table. Column a is used as the primary partition key and column b is used as the secondary partition key within each primary partition defined by column a. To perform queries on a table partitioned in this manner, include the (a=?) or (a=?AND b=?) condition in the queries to ensure that the queries are correctly and efficiently routed to corresponding physical partitions.
HASH(a,b): This method uses a non-hierarchical combination of the partition key columns a and b to partition a table. To perform queries on a table partitioned in this manner, include the (a=?AND b=?) condition in the queries to ensure that the queries are correctly and efficiently routed to corresponding physical partitions.
CO_HASH(a,b): This method uses partition key columns a and b in a unique "OR" relationship to partition a table. To perform queries on a table partitioned in this manner, include the (a=?), (b=?), or (a=? OR b=?) condition in the queries to ensure that the queries are correctly and efficiently routed to corresponding physical partitions.
Whether you can apply a partition function to each of the partition key columns separately
KEY/HASH:
If you use a vector partition key, you cannot apply a partition function to the partition key columns.
If you use a single-column partition key, you can apply only a HASH partition function to the single partition key column. Example: HASH(SUBSTR(a,4)).
CO_HASH:
CO_HASH partitioning requires two to five partition key columns.
You can apply a partition function to each of the partition key columns separately. Example:
PARTITION BY CO_HASH(RIGHT(`order_id`, 4),RIGHT(`buyer_id`,4))
For more information about the similarities and differences between CO_HASH and HASH/KEY, see CREATE TABLE(AUTO mode).
Partition the table based on all partition key columns that have a consistent mapping relationship in their values
Assume that the last six digits of the order_id and buyer_id columns in each row of a table named orders have a consistent mapping relationship. If you want to partition the table based on the last six digits of the order_id and buyer_id columns and ensure that queries with equality conditions on the two columns for a row are routed to the same partition, you can use the following syntax:
CREATE TABLE t_orders(
id bigint not null auto_increment,
seller_id bigint,
order_id bigint,
buyer_id bigint,
order_time datetime not null,
primary key(id)
)
PARTITION BY CO_HASH(
RIGHT('order_id',6)/* Use the last six digits of column c1*/,
RIGHT('buyer_id',6)/* Use the last six digits of column c2*/
)
PARTITIONS 8;Partition the table based on one or more of the partition key columns that have a consistent mapping relationship in their values
Assume that the last eight digits of the order_id column are consistently mapped with the buyer_id column (the length of this value is always 8) in each row of a table named orders. If you want to partition the table based on the last six digits of the order_id or buyer_id column and ensure that queries are routed to the corresponding partition when you use an equality condition on the order_id or buyer_id column, you can use the following syntax:
CREATE TABLE orders(
id bigint not null auto_increment,
bid int,
seller_id bigint,
buyer_id bigint,
order_id bigint,
order_time datetime not null,
primary key(id)
)
PARTITION BY CO_HASH(RIGHT(`order_id`, 8),`buyer_id`)
PARTITIONS 8;Create level-2 partitions
Create HASH-* level-2 partitions
Create HASH-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_h_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4
Non-templated syntax
CREATE TABLE tb_h_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY HASH(id)
(
PARTITION p1 SUBPARTITIONS 2,
PARTITION p2 SUBPARTITIONS 4
)Create HASH-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_h_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 4
SUBPARTITION BY KEY(id)
SUBPARTITIONS 2Non-templated syntax
CREATE TABLE tb_h_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY KEY(id)
(
PARTITION p1 SUBPARTITIONS 2,
PARTITION p2 SUBPARTITIONS 4
)Create HASH-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_h_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
SUBPARTITION sp1 VALUES LESS THAN(1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_h_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES LESS THAN(1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES LESS THAN(1000),
SUBPARTITION sp4 VALUES LESS THAN(2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create HASH-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_h_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(name,id) (
SUBPARTITION sp1 VALUES LESS THAN('Jack',1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_h_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(name,id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES LESS THAN('Jack',1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES LESS THAN('Bob',1000),
SUBPARTITION sp4 VALUES LESS THAN('Tom',2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
)Create HASH-LIST level-2 partitions
Templated syntax
CREATE TABLE tb_h_l_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY LIST (name) (
SUBPARTITION sp1 VALUES IN ('Jack','Bob'),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_h_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY LIST (name)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES IN ('Jack','Bob'),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES IN ('Jack','Bob'),
SUBPARTITION sp4 VALUES IN ('Tom','Lily'),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create HASH-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_h_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS (name,id) (
SUBPARTITION sp1 VALUES IN (('Jack',1000),('Bob',2000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_h_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(bid)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(name,id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES IN (('Jack',1000),('Bob', 2000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES IN (('Bob',1000), ('Tom',2000)),
SUBPARTITION sp4 VALUES IN (('Lily',3000)),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create KEY-* level-2 partitions
Create KEY-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_k_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid, name)
PARTITIONS 2
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4
Non-templated syntax
CREATE TABLE tb_k_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid, name)
PARTITIONS 2
SUBPARTITION BY HASH(id)
(
PARTITION p1 SUBPARTITIONS 2,
PARTITION p2 SUBPARTITIONS 4
)Create KEY-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_k_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY KEY(id)
SUBPARTITIONS 4Non-templated syntax
CREATE TABLE tb_k_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY KEY(id)
(
PARTITION p1 SUBPARTITIONS 2,
PARTITION p2 SUBPARTITIONS 4
)Create KEY-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_k_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
SUBPARTITION sp1 VALUES LESS THAN(1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_k_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES LESS THAN(1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES LESS THAN(1000),
SUBPARTITION sp4 VALUES LESS THAN(2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create KEY-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_k_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id) (
SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_k_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
),
PARTITION p2 (
SUBPARTITION sp3 VALUES LESS THAN('2000-01-01',1000),
SUBPARTITION sp4 VALUES LESS THAN('2020-01-01',2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
)Create KEY-LIST level-2 partitions
Templated syntax
Non-templated syntax
CREATE TABLE tb_k_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST(id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES IN (1000,2000),
SUBPARTITION sp2 VALUES IN (3000,4000),
SUBPARTITION sp3 VALUES In (DEFAULT)
),
PARTITION p2 (
SUBPARTITION sp4 VALUES IN (5000,6000),
SUBPARTITION sp5 VALUES In (DEFAULT)
)
)Create KEY-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_k_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
SUBPARTITION sp3 VALUES In (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_k_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
PARTITION p1 (
SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
SUBPARTITION sp3 VALUES In (DEFAULT)
),
PARTITION p2 (
SUBPARTITION sp4 VALUES IN (('2002-01-10',5000),('2002-02-10',6000)),
SUBPARTITION sp5 VALUES In (DEFAULT)
)
)Create RANGE-* level-2 partitions
Create RANGE-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_r_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY HASH(bid)
SUBPARTITIONS 4
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY HASH(bid)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)Create RANGE-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_r_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY KEY(bid,name)
SUBPARTITIONS 4
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY KEY(bid,name)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)Create RANGE-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_r_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY RANGE(bid)
(
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY RANGE(bid)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE) (
SUBPARTITION sp3 VALUES LESS THAN(100),
SUBPARTITION sp4 VALUES LESS THAN(200),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create RANGE-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_r_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY RANGE COLUMNS(bid, name)
(
SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY RANGE COLUMNS(bid, name)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE) (
SUBPARTITION sp3 VALUES LESS THAN(100, 'Tom'),
SUBPARTITION sp4 VALUES LESS THAN(200, 'Bob'),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE, MAXVALUE)
)
)Create RANGE-LIST level-2 partitions
Templated syntax
CREATE TABLE tb_r_l_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY LIST(name)
(
SUBPARTITION sp1 VALUES IN ('Jack'),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY LIST(name)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
SUBPARTITION sp1 VALUES IN ('Jack'),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE) (
SUBPARTITION sp3 VALUES IN ('Tom'),
SUBPARTITION sp4 VALUES IN ('Bob'),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create RANGE-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_r_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY LIST COLUMNS(name,bid)
(
SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
PARTITION p2 VALUES LESS THAN(MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_r_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE(TO_DAYS(birthday))
SUBPARTITION BY LIST COLUMNS(name,bid)
(
PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE) (
SUBPARTITION sp3 VALUES IN (('Jack', 100), ('Bob',200)),
SUBPARTITION sp4 VALUES IN (('Lily', 200), ('Marry',400)),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create RANGE COLUMNS-* level-2 partitions
Create RANGE COLUMNS-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_rc_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY HASH(bid)
SUBPARTITIONS 4
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY HASH(bid)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') SUBPARTITIONS 2,
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) SUBPARTITIONS 3
)Create RANGE COLUMNS-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_rc_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY KEY(bid,id)
SUBPARTITIONS 4
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY KEY(bid,id)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') SUBPARTITIONS 2,
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) SUBPARTITIONS 3
)Create RANGE COLUMNS-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_rc_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid)
(
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
SUBPARTITION sp3 VALUES LESS THAN(100),
SUBPARTITION sp4 VALUES LESS THAN(200),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create RANGE COLUMNS-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_rc_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE COLUMNS(bid,id)
(
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid,id)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
SUBPARTITION sp3 VALUES LESS THAN(200,1000),
SUBPARTITION sp4 VALUES LESS THAN(200,2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
)Create RANGE COLUMNS-LIST level-2 partitions
Templated syntax
CREATE TABLE tb_rc_l_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST(bid)
(
SUBPARTITION sp1 VALUES IN (100,200),
SUBPARTITION sp2 VALUES IN (300,400),
SUBPARTITION sp3 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST(bid)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
SUBPARTITION sp1 VALUES IN (100,200),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
SUBPARTITION sp3 VALUES IN (100,200),
SUBPARTITION sp4 VALUES IN (300,400),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create RANGE COLUMNS-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_rc_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id)
(
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000)),
SUBPARTITION sp2 VALUES IN ((300,300),(400,2000)),
SUBPARTITION sp3 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)Non-templated syntax
CREATE TABLE tb_rc_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id)
(
PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000)),
SUBPARTITION sp2 VALUES IN ((300,3000),(400,2000)),
SUBPARTITION sp3 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create LIST-* level-2 partitions
Create LIST-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_l_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY HASH(bid)
SUBPARTITIONS 4
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY HASH(bid)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) SUBPARTITIONS 3,
PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)Create LIST-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_l_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY KEY(bid, name)
SUBPARTITIONS 4
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY HASH(bid,name)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')) SUBPARTITIONS 3,
PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)Create LIST-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_l_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY RANGE(bid)
(
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY RANGE(bid)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES LESS THAN(100),
SUBPARTITION sp4 VALUES LESS THAN(200),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create LIST-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_l_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY RANGE COLUMNS(bid, id)
(
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY RANGE COLUMNS(bid, id)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES LESS THAN(100,1000),
SUBPARTITION sp4 VALUES LESS THAN(200,2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
)Create LIST-LIST level-2 partitions
Templated syntax
CREATE TABLE tb_l_l_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY LIST(id)
(
SUBPARTITION sp1 VALUES IN (1000,2000,3000),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY LIST(id)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
SUBPARTITION sp1 VALUES IN (1000,2000,3000),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES IN (1000,2000,3000),
SUBPARTITION sp4 VALUES IN (4000,5000),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create LIST-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_l_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY LIST COLUMNS(bid,id)
(
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_l_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
SUBPARTITION BY LIST COLUMNS(bid,id)
(
PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create LIST COLUMNS-* level-2 partitions
Create LIST COLUMNS-HASH level-2 partitions
Templated syntax
CREATE TABLE tb_lc_h_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY HASH(bid)
SUBPARTITIONS 4
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_h_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY HASH(bid)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)Create LIST COLUMNS-KEY level-2 partitions
Templated syntax
CREATE TABLE tb_lc_k_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY KEY(bid,id)
SUBPARTITIONS 4
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_k_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY KEY(bid,id)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)Create LIST COLUMNS-RANGE level-2 partitions
Templated syntax
CREATE TABLE tb_lc_r_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid)
(
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_r_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
SUBPARTITION sp1 VALUES LESS THAN(100),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES LESS THAN(100),
SUBPARTITION sp4 VALUES LESS THAN(200),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
)
)Create LIST COLUMNS-RANGE COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_lc_rc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY RANGE COLUMNS(bid,id)
(
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_rc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY RANGE COLUMNS(bid,id)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
SUBPARTITION sp1 VALUES LESS THAN(100,1000),
SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES LESS THAN(100,1000),
SUBPARTITION sp4 VALUES LESS THAN(200,2000),
SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
)Create LIST COLUMNS-LIST level-2 partitions
Templated syntax
CREATE TABLE tb_lc_l_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY LIST(id)
(
SUBPARTITION sp1 VALUES IN (1000,2000,3000),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_l_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY LIST(id)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
SUBPARTITION sp1 VALUES IN (1000,2000,3000),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES IN (1000,2000),
SUBPARTITION sp4 VALUES IN (3000,4000,5000),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)Create LIST COLUMNS-LIST COLUMNS level-2 partitions
Templated syntax
CREATE TABLE tb_lc_lc_tp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id)
(
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
PARTITION p2 VALUES IN (DEFAULT)
)Non-templated syntax
CREATE TABLE tb_lc_lc_ntp(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id)
(
PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp2 VALUES IN (DEFAULT)
),
PARTITION p2 VALUES IN (DEFAULT) (
SUBPARTITION sp3 VALUES IN ((100,1000),(200,2000),(300,3000)),
SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
SUBPARTITION sp5 VALUES IN (DEFAULT)
)
)MySQL syntax used to select a partition
You can use the MySQL syntax in a partitioned table of a database in AUTO mode to read data from the partition specified by the syntax.
Syntax
SELECT ... FROM tbl_name [PARTITION ( part_name[, part_name, ...] )]Select level-1 partitions
Example 1: Query data in one or more level-1 partitions of a specified partitioned table
CREATE TABLE tb_k(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(id, bid)
-> PARTITIONS 8;
Query OK, 0 rows affected (2.06 sec)
explain SELECT * FROM tb_k PARTITION( p1,p2 );
+-----------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-----------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k[p1,p2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k` AS `tb_k`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: e210fe50 |
+-----------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.05 sec)
Example 2: Delete data from one or more level-1 partitions of a specified partitioned table
CREATE TABLE tb_k(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(id, bid)
-> PARTITIONS 8;
Query OK, 0 rows affected (3.59 sec)
explain DELETE FROM tb_k PARTITION( p1,p2 );
+---------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+---------------------------------------------------------------------------------+
| LogicalModifyView(tables="tb_k[p1,p2]", shardCount=2, sql="DELETE FROM `tb_k`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 19bd2adf |
+---------------------------------------------------------------------------------+
Select level-2 partitions
Example 3: Query data in one or more level-1 or level-2 partitions of a specified partitioned table
CREATE TABLE tb_k_k_tp(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(bid,name)
-> PARTITIONS 2
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.94 sec)
explain SELECT * FROM tb_k_k_tp PARTITION( p1sp1,p1sp2 )/* Specify two level-2 partitions.*/;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 38bba74d |
+--------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)
explain SELECT * FROM tb_k_k_tp PARTITION( p1,p2sp2 )/* Specify a level-1 partition and a level-2 partition.*/;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2,p1sp3,p1sp4,p2sp2]", shardCount=5, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: dbc4cb56 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
Naming rules for partitions
To access or modify a specified level-1 or level-2 partition, you must specify the name of the level-1 or level-2 partition in the syntax. This section describes the naming rules that enable the system to automatically generate partition names in a partitioned table.
The system automatically generates partition names for a partitioned table in the following scenarios:
The manual, automatic, HASH, or KEY partitioning method is used for a partitioned table or a global secondary index (GSI) table.
Templated level-2 partitions are created in a partitioned table.
HASH partitioned tables are repartitioned and changed.
Naming rules for level-1 partitions
If the manual, automatic, HASH, or KEY partitioning method is used and the number of HASH partitions is specified for level-1 partitions in a partitioned table, the system automatically generates partition names for the level-1 partitions.
When the HASH or KEY partitioning method is used, partitions are named based on the following rule: p + serial number in the subscript format. The serial number starts from 1.
CREATE TABLE test_tbl_part_name(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(id)
PARTITIONS 4For example, if you execute the preceding statements to create a partitioned table, partition names are generated in a sequential manner and start from p1. p1 is the name of the first HASH partition and p2 is the name of the second HASH partition. Verified results:
CREATE TABLE test_tbl_part_name (
-> id bigint not null auto_increment,
-> primary key(id)
-> )
-> PARTITION BY KEY(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (1.13 sec)
select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p1 |
| test_tbl_part_name | p2 |
| test_tbl_part_name | p3 |
| test_tbl_part_name | p4 |
+--------------------+----------------+
4 rows in set (0.59 sec)If you execute the ALTER statement to repartition and change a specified partition in a partitioned table, new partitions are named based on the following rule: p + the serial number of the current largest partition in the subscript format + 1. Example:
alter table test_tbl_part_name split partition p1;
Query OK, 0 rows affected (5.83 sec)
select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p5 |
| test_tbl_part_name | p6 |
| test_tbl_part_name | p2 |
| test_tbl_part_name | p3 |
| test_tbl_part_name | p4 |
+--------------------+----------------+
5 rows in set (0.18 sec)Naming rules for level-2 partitions
Level -2 partitions support templated and non-templated syntax. Therefore, naming rules for level-2 partitions are more complicated than those for level-1 partitions.
For templated level-2 partitions, templated HASH partitions are named based on the following rule: sp + the serial number of the level-2 partition in the subscript format. The serial number starts from 1.
For non-templated level-2 partitions, the names of HASH partitions under each level-1 partition are independently generated and adhere to the following rule: sp + the serial number of the level-2 partition in the subscript format. The serial number starts from 1 and each level-1 partition is independently counted.
All level-2 partitions are named based on the following rule: Name of the level-1 partition + Name of the templated level-2 partition.
For example, you can execute the following statements to create a templated level-2 partitioned table:
CREATE TABLE test_tbl_part_name2(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(bid)
PARTITIONS 2
SUBPARTITION BY KEY(id)
SUBPARTITIONS 4The names of all level-2 partitions in the table are generated in the following manner:
HASH partition names p1 and p2 are automatically generated for level-1 partitions.
HASH partition names sp1, sp2, sp3, and sp4 are automatically generated for templated level-2 partitions.
The actual names of level-2 partitions (physical partition names) are generated by combining the names of the level-1 and level-2 partitions.
p1sp1, p1sp2, p1sp3, and p1sp4
p2sp1, p2sp2, p2sp3, and p2sp4
Queried partition information:
CREATE TABLE test_tbl_part_name2(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(bid)
-> PARTITIONS 2
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.40 sec)
select table_name,partition_name,subpartition_name from information_schema.partitions where table_schema='autodb2' and table_name='test_tbl_part_name2';
+---------------------+----------------+-------------------+
| table_name | partition_name | subpartition_name |
+---------------------+----------------+-------------------+
| test_tbl_part_name2 | p1 | p1sp1 |
| test_tbl_part_name2 | p1 | p1sp2 |
| test_tbl_part_name2 | p1 | p1sp3 |
| test_tbl_part_name2 | p1 | p1sp4 |
| test_tbl_part_name2 | p2 | p2sp1 |
| test_tbl_part_name2 | p2 | p2sp2 |
| test_tbl_part_name2 | p2 | p2sp3 |
| test_tbl_part_name2 | p2 | p2sp4 |
+---------------------+----------------+-------------------+
8 rows in set (0.12 sec)
View partition information
PolarDB-X allows you to query the metadata of a partitioned table such as partition topology, by using multiple methods.
Use PARTITIONS views
PolarDB-X is compatible with the INFORMATION_SCHEMA.PARTITIONS view of MySQL. PolarDB-X allows you to use the PARTITIONS view to query the metadata of each level -1 partition and its level-2 partitions. Example:
select *
-> from information_schema.partitions
-> where table_schema='autodb2' and table_name='test_tbl_part_name2'
-> order by partition_name, subpartition_name;
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | SUBPARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def | autodb2 | test_tbl_part_name2 | p1 | p1sp1 | 1 | 1 | KEY | KEY | bid | id | 1 | -4611686018427387899 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p1 | p1sp2 | 1 | 2 | KEY | KEY | bid | id | 1 | 3 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p1 | p1sp3 | 1 | 3 | KEY | KEY | bid | id | 1 | 4611686018427387905 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p1 | p1sp4 | 1 | 4 | KEY | KEY | bid | id | 1 | 9223372036854775807 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p2 | p2sp1 | 2 | 1 | KEY | KEY | bid | id | 9223372036854775807 | -4611686018427387899 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p2 | p2sp2 | 2 | 2 | KEY | KEY | bid | id | 9223372036854775807 | 3 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p2 | p2sp3 | 2 | 3 | KEY | KEY | bid | id | 9223372036854775807 | 4611686018427387905 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
| def | autodb2 | test_tbl_part_name2 | p2 | p2sp4 | 2 | 4 | KEY | KEY | bid | id | 9223372036854775807 | 9223372036854775807 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL |
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
8 rows in set (0.14 sec)
Use the SHOW TOPOLOGY command
You can also run the SHOW TOPOLOGY FROM #table_name command to query the overall topology of the partitioned table and the physical location of each partition in the table (the data node (DN) on which the physical table is created). Example:
SHOW TOPOLOGY FROM test_tbl_part_name2 ORDER BY PARTITION_NAME, SUBPARTITION_NAME;
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
| ID | GROUP_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PHY_DB_NAME | DN_ID |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
| 4 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00000 | p1 | p1sp1 | autodb2_p00000 | polardbx-storage-0-master |
| 0 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00001 | p1 | p1sp2 | autodb2_p00001 | polardbx-storage-1-master |
| 5 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00002 | p1 | p1sp3 | autodb2_p00000 | polardbx-storage-0-master |
| 1 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00003 | p1 | p1sp4 | autodb2_p00001 | polardbx-storage-1-master |
| 6 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00004 | p2 | p2sp1 | autodb2_p00000 | polardbx-storage-0-master |
| 2 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00005 | p2 | p2sp2 | autodb2_p00001 | polardbx-storage-1-master |
| 7 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00006 | p2 | p2sp3 | autodb2_p00000 | polardbx-storage-0-master |
| 3 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00007 | p2 | p2sp4 | autodb2_p00001 | polardbx-storage-1-master |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
8 rows in set (0.07 sec)Use the TABLE_DETAILS view
You can use the INFORMATION_SCHEMA.TABLE_DETAIL view to query the percentage of data in each partition of the partitioned table and its read/write information. Example:
select table_name, partition_name, subpartition_name, percent,
-> rows_read, rows_inserted, rows_updated, rows_deleted from information_schema.table_detail
-> where table_schema='autodb2' and table_name='test_tbl_part_name2'
-> order by partition_name, subpartition_name;
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
| table_name | partition_name | subpartition_name | percent | rows_read | rows_inserted | rows_updated | rows_deleted |
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
| test_tbl_part_name2 | p1 | p1sp1 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p1 | p1sp2 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p1 | p1sp3 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p1 | p1sp4 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p2 | p2sp1 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p2 | p2sp2 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p2 | p2sp3 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
| test_tbl_part_name2 | p2 | p2sp4 | 0.00%├------------------- | 0 | 0 | 0 | 0 |
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
8 rows in set (0.10 sec)You can also use the INFORMATION_SCHEMA.TABLE_DETAIL view to query the metadata of PolarDB-X tables, such as the table group to which the partitioned table belongs and the GSI of the table.