Change the type and partitioning rule of a table - PolarDB

This topic describes the syntaxes that are used to partition a table based on partition functions and partition key columns and change the type of a table to a partitioned table, broadcast table or unpartitioned table. This topic also provides sample code to show how to use the syntaxes.

Prerequisites

The MODE parameter is set to auto/partitioning when you create the logical database. For more information, see CREATE DATABASE.
If you want to change the type or partitioning rule of a table, the kernel version of the PolarDB-X instance in which the table is deployed must be V5.4.13 or later.
If you want to change the partitions of a partitioned table that contains a global secondary index (GSI), the kernel version of the PolarDB-X instance in which the table is deployed must be V5.4.13 or later.
If you want to change a standard partitioned table to a table partitioned based on the default primary key, the kernel version of the PolarDB-X instance in which the standard partitioned table must be V5.4.14 or later.
If you want to change the number of partitions of a table partitioned based on the default primary key, the kernel version of the PolarDB-X instance in which the table is deployed must be V5.4.14 or later.

For information about how to view the kernel version of a PolarDB-X instance, see View the version of an instance.

Usage notes

After you change the partitioning rule of a table that is automatically partitioned based on the primary key, the table becomes a standard table. In this case, the automatic partitioning rules and the index changing rules do not take effect on the table. For more information, see Automatic sharding in automatic partitioning mode.

In this topic, a table named t_order1 is used in the examples to show how to change the type of a table. You can use the following statement to create the t_order1 table:

CREATE TABLE t_order1 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table types

PolarDB-X instances support three types of tables: partitioned tables, broadcast tables, and non-partitioned tables. You can use the ALTER TABLE statement to change the type of a table. You can also modify the partitioning rule of a partitioned table.

Partitioned tables

Partitioned tables are created by using the partition_options clause.

You can use the following partition_options clause to partition a table:

  partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec

Note For more information about partitioning rules, see Partitioning methods.

Broadcast tables
You can use a BROADCAST clause to create a broadcast table. The system creates the same broadcast table on each database shard and uses the distributed transaction mode to ensure that data is consistent among tables. For more information, see Create a non-partitioned table and a broadcast table.
Non-partitioned tables
Tables that are created by using the SINGLE clause are not partitioned. For more information, see Create a non-partitioned table and a broadcast table.

Change a non-partitioned table or broadcast table to a partitioned table

Syntax
```
ALTER TABLE table_name partition_options;
```
Note
For more information about partition_options, see CREATE TABLE (AUTO mode).
Example
For example, you use a non-partitioned table named t_order1 to store your business data. As your business grows, the table cannot handle the increasing amount of business data. To resolve this issue, you can execute the following statement to change the table to a partitioned table. In the statement, order_id is used as the partition key.
```
ALTER TABLE t_order1 PARTITION BY KEY(`order_id`);
```
You can also execute the following statement to specify the number of partitions of the partitioned table:
```
ALTER TABLE t_order1 PARTITION BY KEY(`order_id`) PARTITIONS 8;
```

Change a non-partitioned table or partitioned table to a broadcast table

Syntax
```
ALTER TABLE table_name BROADCAST;
```
Example
You can execute the following statement to change a non-partitioned table or partitioned table to a broadcast table. The table used in the following example is named t_order1.
```
ALTER TABLE t_order1 BROADCAST;
```

Change a broadcast table or partitioned table to a non-partitioned table

Syntax
```
ALTER TABLE table_name SINGLE;
```
Example
You can execute the following statement to change a broadcast table or partitioned table to a non-partitioned table. The table used in the following example is named t_order1.
```
ALTER TABLE t_order1 SINGLE;
```

Modify the partitioning rule of a partitioned table

Syntax
```
ALTER TABLE tbl_name partition_options;
```

Example 1

You can execute the following statement to create a partitioned table named t_order in a PolarDB-X database. The order_id column is used as the partition key of the table.

  CREATE TABLE t_order (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `order_id` varchar(20) DEFAULT NULL,
    `buyer_id` varchar(20) DEFAULT NULL,
    `seller_id` varchar(20) DEFAULT NULL,
    `order_snapshot` longtext DEFAULT NULL,
    `order_detail` longtext DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `l_i_order` (`order_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  PARTITION BY KEY(`order_id`);

In this example, the partitioning rule of t_order needs to be changed to a new partitioning rule that meets the following requirements:

The table is partitioned by using key partitioning based on the order_id and buyer_id columns.
The table is partitioned into eight partitions.

You can execute the following statement to modify the partitioning rule:

ALTER TABLE t_order PARTITION BY KEY(order_id, buyer_id) PARTITIONS 8;

Execute the show create table t_order statement after you modify the partitioning rule. The following information is returned:

CREATE TABLE `t_order` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  `order_detail` longtext,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`),
  KEY `auto_shard_key_order_id_buyer_id` USING BTREE (`order_id`, `buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`order_id`,`buyer_id`)
PARTITIONS 8

Example 2

You can execute the following statement to create a table named t_order in a PolarDB-X database. The table is partitioned by using range partitioning based on the id column.

  CREATE TABLE t_order (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `order_id` varchar(20) DEFAULT NULL,
    `buyer_id` varchar(20) DEFAULT NULL,
    `seller_id` varchar(20) DEFAULT NULL,
    `order_snapshot` longtext DEFAULT NULL,
    `order_detail` longtext DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `l_i_order` (`order_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  PARTITION BY RANGE(`id`)
  (
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (1000),
    PARTITION P3 VALUES LESS THAN MAXVALUE
  );

In this example, the partitioning rule of t_order needs to be changed to a new partitioning rule that meets the following requirements:

The table is partitioned by using key partitioning based on the order_id and buyer_id columns.
The table is partitioned into 16 partitions.

You can execute the following statement to modify the partitioning rule:

ALTER TABLE t_order PARTITION BY KEY(order_id, buyer_id) PARTITIONS 16;

Execute the show create table t_order statement after you modify the partitioning rule. The following information is returned:

CREATE TABLE `t_order` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  `order_detail` longtext,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`),
  KEY `auto_shard_key_order_id_buyer_id` USING BTREE (`order_id`, `buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`order_id`,`buyer_id`)
PARTITIONS 16

Change a standard partitioned table to a table partitioned based on the default primary key

Syntax

ALTER TABLE table_name REMOVE PARTITIONING;

Example
You can execute the following statement to create a partitioned table named t_order in a PolarDB-X database. The order_id column is used as the partition key of the table.
```
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY KEY(`order_id`);
```
In this example, the partitioning rule of t_order needs to be changed to a new partitioning rule that meets the following requirements:
- The table is partitioned by using key partitioning based on the primary key column id.
- After the show create table t_order statement is executed, no partition information is displayed.
You can execute the following statement to modify the partitioning rule:
```
ALTER TABLE t_order REMOVE PARTITIONING;
```
Execute the show create table t_order statement after you modify the partitioning rule. The following information is returned:
```
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
Important
- After you modify the partitioning rule, the existing global indexes of the original table are retained and the local indexes change to global indexes.
- You cannot directly change a non-partitioned table or broadcast table to a table partitioned based on the default primary key.

Change the number of partitions of a table partitioned based on the default primary key

Syntax
```
ALTER TABLE table_name PARTITIONS partition_count;
```
Note You can use the statement to change the number of partitions only for a table that is partitioned based on the default primary key. The table is still a table partitioned based on the default primary key after you change the number of its partitions.

Example

You can execute the following statement to create a table named t_order in a PolarDB-X database. By default, the table is partitioned by using key partitioning based on the id column.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After you execute the show full create table t_order statement, the partitioning method and the number of partitions of the table are displayed:

CREATE PARTITION TABLE `t_order` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  `order_detail` longtext,
  PRIMARY KEY (`id`),
  GLOBAL INDEX /* l_i_order_$d8df */ `l_i_order` (`order_id`) PARTITION BY KEY (`order_id`, `id`) PARTITIONS 16,
  LOCAL KEY `_local_l_i_order` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 16

In this example, the partitioning rule of t_order needs to be changed to a new partitioning rule that meets the following requirements:

The number of partitions is changed from 16 to 32.
The table partitioned based on the default primary key is not changed to a standard partitioned table.
The number of partitions of the partitioned table global index is changed.

You can execute the following statement to modify the partitioning rule:

ALTER TABLE t_order PARTITIONS 32;

Execute the show create table t_order statement after you modify the partitioning rule. The following information is returned:

CREATE PARTITION TABLE `t_order` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  `order_detail` longtext,
  PRIMARY KEY USING BTREE (`id`),
  GLOBAL INDEX /* l_i_order_$d8df */ `l_i_order` (`order_id`) PARTITION BY KEY (`order_id`, `id`) PARTITIONS 32,
  LOCAL KEY `_local_l_i_order` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 32

References

After the partitioning rule of a table is modified, you can view the new partitioning rule and the topology of the table.

To view the new partitioning rule of the table, use the SHOW CREATE TABLE tablename statement.
To view the topology of the table, use the SHOW TOPOLOGY FROM table name statement. For more information, see SHOW TOPOLOGY FROM tablename.

FAQ

Why does the execution of a DDL statement that changes the partition keys of a table fail? How do I resolve this issue?

Errors such as instance failures or unique index conflicts can cause DDL execution failures. DDL execution failures do not impact data in the table or block the executions of DML statements or query statements. When the execution of a DDL statement that changes a partition key fails, you can use the CANCEL DDL statement to cancel the operation and then execute the DDL statement again. For more information about theCANCEL DDL statement, see CANCEL DDL.