tencent cloud

Exchange Subpartition Template
Download PDF
Last updated: 2024-10-09 10:27:19
Exchange Subpartition Template
Last updated: 2024-10-09 10:27:19
Download PDF

Feature Overview

The Exchange Subpartition Template feature allows you to exchange subpartition templates between two partitions. In this way, you can quickly change the partitioning policy of a partitioned table, without recreating a table or migrating data.

Supported Versions

The kernel version should be 3.1.15 or later for TXSQL 8.0.

Applicable Scenarios

It is applicable to scenarios where quick data exchange between tables is required.

Use Instructions

Syntax

ALTER TABLE pt EXCHANGE SUBPARTITION TEMPLATE p WITH TABLE nt WITH VALIDATION / WITHOUT VALIDATION

Example

-- Move data from hash_range_1 to hash_range_2 in s20240111.

CREATE TABLE hash_range_1(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
SUBPARTITION BY RANGE(tdsql_day(hired))
SUBPARTITION TEMPLATE
(SUBPARTITION s20240111 VALUES LESS THAN (20240111),
 SUBPARTITION s20240112 VALUES LESS THAN (20240112),
 SUBPARTITION s20240113 VALUES LESS THAN (20240113))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- (hash_range_2 is identical to hash_range_1.)
CREATE TABLE hash_range_2(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
SUBPARTITION BY RANGE(tdsql_day(hired))
SUBPARTITION TEMPLATE
(SUBPARTITION s20230111 VALUES LESS THAN (20240111),
 SUBPARTITION s20230112 VALUES LESS THAN (20240112),
 SUBPARTITION s20230113 VALUES LESS THAN (20240113))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- Create a temp table with the same partitions and table structure as hash_range_1.
CREATE TABLE temp(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- Syntax demonstration
-- Step 1: hash_range_1 --> temp
ALTER TABLE  hash_range_1 
EXCHANGE subpartition template s20240111
with table temp with validation;
-- Step 2: temp --> hash_range_2
ALTER TABLE  hash_range_2 
EXCHANGE subpartition template s20240111
with table temp with validation;

Use Limits

The pt and nt tables cannot have global indexes.
The pt and nt tables cannot have triggers and check constraints.
The partition type and subpartition type of the pt and nt tables only support RANGE and LIST.
The pt and nt tables should have the same definition of partitions.

Data Validation

As described in the official documentation, you can explicitly specify WITH VALIDATION to enable data validation or WITHOUT VALIDATION to disable data validation. Data validation is enabled by default.
Data validation is primarily used to verify whether the data in a partition of the nt table meets the definition of subpartitions in the corresponding partition of the pt table. In the example above, if data of the hired column in the p1 partition of the temp table meets tdsql_day(hired) >= 20240111, it does not meet the definition of subpartitions, SUBPARTITION s20240111 VALUES LESS THAN (20240111), so the data validation will fail.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback