Hi,
After a tough development period the requirements on partitioning and particularly its
management is now getting closer and closer to release into the 5.1 tree so I'll take the
opportunity to present some of the possibilities of partition management soon to be
available.
1) Drop partitions
ALTER TABLE t1 DROP PARTITION (x0, x1);
This command can be used on RANGE/LIST partitions to drop any partitions.
For RANGE partitions the range of the dropped partitions will be inherited by the
remaing partitions. The data of the partitions will be lost.
ALTER TABLE t1 COALESCE PARTITION 2;
This command can be used to drop partitions in a hash partition. No data is lost.
If partitioning is BY LINEAR KEY/HASH only a few of the partitions need to reorganised,
for normal hash partitioning all partitions must be reorganised.
2) Add partitions
ALTER TABLE t1 ADD PARTITION (PARTITION x0 VALUES LESS THAN (10));
This command can be used to add partitions for all types of partitions.
For RANGE/LIST partitions it adds a new empty partition. For HASH partitions
data is reorganised with the new number of partitions. LINEAR keyword will also
here mean a faster reorganisation.
3) Merge partitions
ALTER TABLE t1 REORGANIZE PARTITION (x0, x1) INTO
(PARTITION x01 VALUES LESS THAN (10));
REORGANIZE PARTITION can be used by any partitioning type but for hash partitions
the new number of partitions must be the same as those reorganised.
4) Split partitions
ALTER TABLE t1 REORGANIZE PARTITION (x0) INTO
(PARTITION x01 VALUES IN (19,20), PARTITION x02 VALUES IN (21,22));
5) Reorganise data in partitions
ALTER TABLE t1 REORGANIZE PARTITION (x0,x1) INTO
(PARTITION x01 VALUES LESS THAN (19), PARTITION x02 VALUES LESS THAN (22));
This command can be used to change ranges, values for list partitions and simply reorganising
hash partitions.
6) Rebuild partitions
ALTER TABLE t1 REBUILD PARTITION (x0, x1)
Rebuild the partitions, can be a method to remove fragmentation as an example.
7) Optimise, analyse, repair and check partitions
ALTER TABLE t1 OPTIMIZE PARTITION (x0, x1);
ALTER TABLE t1 CHECK PARTITION (x0);
ALTER TABLE t1 ANALYZE PARTITION (x0);
ALTER TABLE t1 REPAIR PARTITION (x0);
8) Change into any new partition scheme
ALTER TABLE t1 PARTITION BY ... (any legal partition syntax)
There is still no way to move data from a partition into another table.
This is on the TODO list for future releases.
Documentation effort is also ongoing and should hopefully be available at the same time
as the first builds of the MySQL 5.1 hits the street. The MySQL 5.1 is already now source
downloadable, most of the commands above already exists there, however not in its
optimised version yet. This code is just entering the review stage.
Rgrds Mikael
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com