MySQL in a Nutshell [58]
Here 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 takes my server 10 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 partitions 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 that 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 is dropping two middle partitions and not the last one. The data contained in the two dropped would be lost if they had any. 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 you want. 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 statement explanation for more information about table partitioning.
ALTER TABLE: Partition administration clauses
ALTER [IGNORE] TABLE table
ANALYZE PARTITION partition |
CHECK PARTITION partition |
OPTIMIZE PARTITION partition |
REBUILD PARTITION partition |
REPAIR PARTITION partition
Because the ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements do not work with partitioned tables, you will have to use the clauses of ALTER TABLE in this subsection instead. They all follow the same syntax format: the clause is followed by a comma-separated list of partitions to be administered.
The ANALYZE PARTITION clause may be used to read and store the indexes of a partition:
ALTER TABLE clients
ANALYZE PARTITION p0, p1, p2;
To check a partition for corrupted data and indexes, use the CHECK PARTITION clause:
ALTER TABLE clients
CHECK PARTITION p0, p1, p2;
Use the OPTIMIZE PARTITION clause to compact a partition in which the data has changed significantly:
ALTER TABLE clients
OPTIMIZE PARTITION p0, p1, p2;
The REBUILD PARTITION clause defragments the given partitions:
ALTER TABLE clients
REBUILD PARTITION p0, p1, p2;
The REPAIR PARTITION clause attempts to repair corrupted partitions, similar to the REPAIR TABLE statement for tables:
ALTER TABLE clients
REPAIR PARTITION p0, p1, p2;
See the CREATE TABLE statement explanation for more information about table partitioning.
ALTER TABLE: Table options
ALTER TABLE table
[TABLESPACE tablespace_name STORAGE DISK]
{ENGINE|TYPE} [=] {BDB|HEAP|ISAM|INNODB|MERGE|MRG_MYISAM|MYISAM}