support REORGANIZE PARTITION for range partitioned table #15000
Description
Description
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)
Suppose that you would like to move all rows representing members born before 1960 into a separate partition. As we have already seen, this cannot be done using ALTER TABLE ... ADD PARTITION. However, you can use another partition-related extension to ALTER TABLE to accomplish this:
ALTER TABLE members REORGANIZE PARTITION n0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
ALTER TABLE ... REORGANIZE is a DDL operation. Split partition may need to move data, so this is a more difficult task than #15005
There are two possible ways to move the data: logical and physical.
It's a bonus point if we can implement the physical one: telling the TiKV part of the range of data will be moved to another range, and after it's done, modify the DDL meta information in TiDB.
How to keep TiDB processing new read/write requests and handling the split operation in the meanwhile is a challenge.
Score
7684
Mentor(s)
Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace
Recommended Skills
- Golang
- DDL