Skip navigation links

MySQL Forums :: Partitioning :: Partition Management in MySQL 5.1


Advanced Search

Partition Management in MySQL 5.1
Posted by: Mikael Ronström ()
Date: October 06, 2005 06:41PM

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

Options: ReplyQuote


Subject Views Written By Posted
Partition Management in MySQL 5.1 11701 Mikael Ronström 10/06/2005 06:41PM
Re: Partition Management in MySQL 5.1 2730 Steven Roussey 10/27/2005 01:10PM
Re: Partition Management in MySQL 5.1 2486 Steven Roussey 10/27/2005 01:16PM
Re: Partition Management in MySQL 5.1 2719 Mikael Ronström 10/27/2005 02:37PM
Re: Partition Management in MySQL 5.1 2656 Mikael Ronström 10/27/2005 02:32PM
Re: Partition Management in MySQL 5.1 2435 Steven Roussey 11/07/2005 07:36PM
Re: Partition Management in MySQL 5.1 2426 roshan shrestha 05/01/2008 05:51AM
Re: Partition Management in MySQL 5.1 2452 Phil Hildebrand 05/01/2008 08:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.