ALTER TABLE - Partition altering clauses

These table partition clauses for the MySQL statement, ALTER TABLE may be used to add or remove partitions in a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
PARTITION BY options | 
ADD PARTITION (definition) | 
COALESCE PARTITION number | 
DROP PARTITION partition | 
REORGANIZE PARTITION partition INTO (definition) | 
REMOVE PARTITIONING

Explanation

These table partition clauses for ALTER TABLE may be used to add or remove partitions in a table. They were added as of version 5.1.6 of MySQL. For partition clauses that analyze, check, optimize, rebuild, and repair partitions in a table, see the ALTER TABLE - Partition administration clauses documentation page. Also, see the CREATE TABLE - Partitioning explanation for more information on table partitioning.

It should be noted that the execution of the partition clauses for ALTER TABLE is very slow. You may not want to use them with live data if you can avoid it. Instead, you might deploy a method of locking the table to be partitioned for read only activities, making a copy of the table, partitioning the new table, and switching the new table with the old one, but keeping the old table as a backup copy in case there are problems.

Examples

On this page, several examples are included of partitioning a MyISAM table. The partition clauses are explained as they are used in each example. Partitioning is visible at the filesystem level, so to start, let's look at a table's files:

ls -1 clients*

clients.frm
clients.MYD
clients.MYI

We used the ls command (because this server is running Linux) at the command-line to get a directory listing of the files for the clients table, in the sales_dept database subdirectory, in the data directory for MySQL. You can see the usual three file types for a MyISAM table.

The PARTITION BY clause can be used to initially partition a table with the ALTER TABLE statement. Any partition options used with the same clause in the CREATE TABLE statement may be used in ALTER TABLE. See the definition of the CREATE TABLE statement for more of such options.

In the following example we alter the table clients using this clause to create partitions:

ALTER TABLE clients
PARTITION BY KEY(client_id)
PARTITIONS 2;

In this statement, we are instructing MySQL to partition the given table by the KEY method using the client_id column. We further tell it to split the table into two partitions. Now let's run the ls command again to see the results at the filesystem level:

ls -1 clients*

clients.frm
clients.par
clients#P#p0.MYD
clients#P#p0.MYI
clients#P#p1.MYD
clients#P#p1.MYI

As you can see, we now have a pair of index and data files for each partition, along with another file related to the partition schema (i.e., the .par file). The table schema file (i.e., the .frm file) remains unchanged.

The ADD PARTITION clause adds a new partition to a table in which partitions are determined based on a range of values. To demonstrate this, let's partition the clients table again, but this time basing the partitioning on a range of values for the client_id column, the primary key. If your table has a primary key, that key must be included in the basis of the partitions.

ALTER TABLE clients
ADD PARTITION (PARTITION p2);

The REMOVE PARTITIONING clause removes partitioning from a table. It shifts data back to one data file and one index file. Below is an example of its use:

ALTER TABLE clients
REMOVE PARTITIONING;

For some situations, the ADD PARTITION clause discussed previously won't work. In particular, it won't work with a table in which the last partition was given the range of MAXVALUE:

ALTER TABLE clients
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
ADD PARTITION (PARTITION p2 VALUES LESS THAN (800));

ERROR 1481 (HY000): 
VALUES LESS THAN value must be strictly increasing for each partition

Instead of ADD PARTITION, the REORGANIZE PARTITION clause can be used to split the data contained in the last partition into two separate partitions. This clause can be used to separate the data in an existing partition into multiple partitions based on their given partition definitions. Below is an example of this clause using the partitions previously described:

ALTER TABLE clients
REORGANIZE PARTITION p1 INTO
(PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN MAXVALUE);

When experimenting with an empty table, this SQL statement took my server ten seconds to execute. Consider this when using this clause or any partitioning clauses with ALTER TABLE

The DROP PARTITION clause may be used to eliminate named partitions in an existing table and to delete the data contained in the dropped partitions. To reduce the number of paritions without loss of data, see the COALESCE PARTITION clause for this same SQL statement. For an example of the DROP PARTITION clause, if you have a table which has six partitions and you want to delete two of them, you could execute an SQL statement like the second one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
DROP PARTITION p1, p2;

Notice that the ALTER TABLE statement here is dropping two middle partitions and not the last one. The data contained in the two dropped will be lost. However, because of the MAXVALUE parameter of the last partition, any new rows of data that have a client_id of 400 or greater will be stored in the p3 partition. Partitions need to be in order, but not sequentially named.

The COALESCE PARTITION clause may be used to reduce the number of partitions in an existing table by the number given. For example, if you have a table that has four partitions and you want to reduce it to three, you could execute a statement like the ALTER TABLE one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY HASH( client_id )
PARTITIONS 4;

ALTER TABLE clients
COALESCE PARTITION 1;

Notice that the PARTITION keyword in this last SQL statement is not plural. Also notice that you give the number of partitions by which you want to reduce the partitions, not the total with which you want to end. If you give a value equal to or greater than the number of partitions in the table, you'll receive an error instructing you that you must use DROP TABLE instead.

See the CREATE TABLE - Partitioning explanation for more information about table partitioning.