13.1.8.1 ALTER TABLE Partition Operations

Partitioning-related clauses for ALTER
TABLE can be used with partitioned tables for
repartitioning, to add, drop, discard, import, merge, and split
partitions, and to perform partitioning maintenance.

Simply using a partition_options
clause with ALTER TABLE on a
partitioned table repartitions the table according to the
partitioning scheme defined by the
partition_options. This clause
always begins with PARTITION BY, and
follows the same syntax and other rules as apply to the
partition_options clause for
CREATE TABLE (for more
detailed information, see Section 13.1.18, “CREATE TABLE Syntax”),
and can also be used to partition an existing table that is
not already partitioned. For example, consider a
(nonpartitioned) table defined as shown here:

CREATE TABLE t1 (
id INT,
year_col INT
);

This table can be partitioned by HASH,
using the id column as the partitioning
key, into 8 partitions by means of this statement:

ALTER TABLE t1
PARTITION BY HASH(id)
PARTITIONS 8;

MySQL supports an ALGORITHM option with
[SUB]PARTITION BY [LINEAR] KEY.
ALGORITHM=1 causes the server to use the
same key-hashing functions as MySQL 5.1 when computing the
placement of rows in partitions;
ALGORITHM=2 means that the server employs
the key-hashing functions implemented and used by default
for new KEY partitioned tables in MySQL
5.5 and later. (Partitioned tables created with the
key-hashing functions employed in MySQL 5.5 and later cannot
be used by a MySQL 5.1 server.) Not specifying the option
has the same effect as using ALGORITHM=2.
This option is intended for use chiefly when upgrading or
downgrading [LINEAR] KEY partitioned
tables between MySQL 5.1 and later MySQL versions, or for
creating tables partitioned by KEY or
LINEAR KEY on a MySQL 5.5 or later server
which can be used on a MySQL 5.1 server.

To upgrade a KEY partitioned table that
was created in MySQL 5.1, first execute
SHOW CREATE TABLE and note
the exact columns and number of partitions shown. Now
execute an ALTER TABLE statement using
exactly the same column list and number of partitions as in
the CREATE TABLE statement, while adding
ALGORITHM=2 immediately following the
PARTITION BY keywords. (You should also
include the LINEAR keyword if it was used
for the original table definition.) An example from a
session in the mysql client is shown
here:

Downgrading a table created using the default key-hashing
used in MySQL 5.5 and later to enable its use by a MySQL 5.1
server is similar, except in this case you should use
ALGORITHM=1 to force the table's
partitions to be rebuilt using the MySQL 5.1 key-hashing
functions. It is recommended that you not do this except
when necessary for compatibility with a MySQL 5.1 server, as
the improved KEY hashing functions used
by default in MySQL 5.5 and later provide fixes for a number
of issues found in the older implementation.

Note

A table upgraded by means of ALTER TABLE ...
PARTITION BY ALGORITHM=2 [LINEAR] KEY ... can no
longer be used by a MySQL 5.1 server. (Such a table would
need to be downgraded with ALTER TABLE ...
PARTITION BY ALGORITHM=1 [LINEAR] KEY ... before
it could be used again by a MySQL 5.1 server.)

The table that results from using an ALTER TABLE
... PARTITION BY statement must follow the same
rules as one created using CREATE TABLE ...
PARTITION BY. This includes the rules governing
the relationship between any unique keys (including any
primary key) that the table might have, and the column or
columns used in the partitioning expression, as discussed in
Section 22.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”.
The CREATE TABLE ... PARTITION BY rules
for specifying the number of partitions also apply to
ALTER TABLE ... PARTITION BY.

The partition_definition clause
for ALTER TABLE ADD PARTITION supports
the same options as the clause of the same name for the
CREATE TABLE statement. (See
Section 13.1.18, “CREATE TABLE Syntax”, for the syntax and
description.) Suppose that you have the partitioned table
created as shown here:

You can add a new partition p3 to this
table for storing values less than 2002
as follows:

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

DROP PARTITION can be used to drop one or
more RANGE or LIST
partitions. This statement cannot be used with
HASH or KEY
partitions; instead, use COALESCE
PARTITION (see below). Any data that was stored in
the dropped partitions named in the
partition_names list is
discarded. For example, given the table
t1 defined previously, you can drop the
partitions named p0 and
p1 as shown here:

The statement just shown has the same effect as the
following DELETE statement:

DELETE FROM t1 WHERE year_col < 1991;

When truncating multiple partitions, the partitions do not
have to be contiguous: This can greatly simplify delete
operations on partitioned tables that would otherwise
require very complex WHERE conditions if
done with DELETE statements.
For example, this statement deletes all rows from partitions
p1 and p3:

TRUNCATE PARTITION is supported only for
partitioned tables that use the
MyISAM,
InnoDB, or
MEMORY storage engine. It also
works on BLACKHOLE tables (but
has no effect). It is not supported for
ARCHIVE tables.

COALESCE PARTITION can be used with a
table that is partitioned by HASH or
KEY to reduce the number of partitions by
number. Suppose that you have
created table t2 as follows:

To reduce the number of partitions used by
t2 from 6 to 4, use the following
statement:

ALTER TABLE t2 COALESCE PARTITION 2;

The data contained in the last
number partitions will be merged
into the remaining partitions. In this case, partitions 4
and 5 will be merged into the first 4 partitions (the
partitions numbered 0, 1, 2, and 3).

To change some but not all the partitions used by a
partitioned table, you can use REORGANIZE
PARTITION. This statement can be used in several
ways:

To merge a set of partitions into a single partition.
This is done by naming several partitions in the
partition_names list and
supplying a single definition for
partition_definition.

To split an existing partition into several partitions.
Accomplish this by naming a single partition for
partition_names and providing
multiple
partition_definitions.

To change the ranges for a subset of partitions defined
using VALUES LESS THAN or the value
lists for a subset of partitions defined using
VALUES IN.

This statement may also be used without the
partition_names INTO
(partition_definitions)
option on tables that are automatically partitioned
using HASH partitioning to force
redistribution of data. (Currently, only
NDB tables are
automatically partitioned in this way.) This is useful
in NDB Cluster where, after you have added new NDB
Cluster data nodes online to an existing NDB Cluster,
you wish to redistribute existing NDB Cluster table data
to the new data nodes. In such cases, you should invoke
the statement with the
ALGORITHM=INPLACE option; in other
words, as shown here:

ALTER TABLE ... ALGORITHM=INPLACE, REORGANIZE
PARTITION does not work with tables which
were created using the MAX_ROWS
option, because it uses the constant
MAX_ROWS value specified in the
original CREATE TABLE
statement to determine the number of partitions
required, so no new partitions are created. Instead,
you can use ALTER TABLE ...
ALGORITHM=INPLACE,
MAX_ROWS=rows to
increase the maximum number of rows for such a table;
in this case, ALTER TABLE ...
ALGORITHM=INPLACE, REORGANIZE PARTITION is
not needed (and causes an error if executed). The
value of rows must be
greater than the value specified for
MAX_ROWS in the original
CREATE TABLE statement for this to
work.

Employing MAX_ROWS to force the
number of table partitions is deprecated in NDB 7.5.4
and later; use PARTITION_BALANCE
instead (see
Setting NDB_TABLE options).

Attempting to use REORGANIZE
PARTITION without the
partition_names INTO
(partition_definitions)
option on explicitly partitioned tables results in the
error REORGANIZE PARTITION without parameters
can only be used on auto-partitioned tables using HASH
partitioning.

Note

For partitions that have not been explicitly named, MySQL
automatically provides the default names
p0, p1,
p2, and so on. The same is true with
regard to subpartitions.

To exchange a table partition or subpartition with a table,
use the ALTER
TABLE ... EXCHANGE PARTITION statement—that
is, to move any existing rows in the partition or
subpartition to the nonpartitioned table, and any existing
rows in the nonpartitioned table to the table partition or
subpartition.

Several options provide partition maintenance and repair
functionality analogous to that implemented for
nonpartitioned tables by statements such as
CHECK TABLE and
REPAIR TABLE (which are also
supported for partitioned tables; for more information, see
Section 13.7.2, “Table Maintenance Statements”). These include
ANALYZE PARTITION, CHECK
PARTITION, OPTIMIZE PARTITION,
REBUILD PARTITION, and REPAIR
PARTITION. Each of these options takes a
partition_names clause consisting
of one or more names of partitions, separated by commas. The
partitions must already exist in the table to be altered.
You can also use the ALL keyword in place
of partition_names, in which case
the statement acts on all table partitions. For more
information and examples, see
Section 22.3.4, “Maintenance of Partitions”.

Some MySQL storage engines, such as
InnoDB, do not support
per-partition optimization. For a partitioned table using
such a storage engine, ALTER TABLE ... OPTIMIZE
PARTITION causes the entire table to rebuilt and
analyzed, and an appropriate warning to be issued. (Bug
#11751825, Bug #42822)

To work around this problem, use the statements
ALTER TABLE ... REBUILD PARTITION and
ALTER TABLE ... ANALYZE PARTITION
instead.

The ANALYZE PARTITION, CHECK
PARTITION, OPTIMIZE PARTITION,
and REPAIR PARTITION options are not
permitted for tables which are not partitioned.

REMOVE PARTITIONING enables you to remove
a table's partitioning without otherwise affecting the table
or its data. This option can be combined with other
ALTER TABLE options such as
those used to add, drop, or rename columns or indexes.

Using the ENGINE option with
ALTER TABLE changes the
storage engine used by the table without affecting the
partitioning.

When ALTER TABLE ... EXCHANGE PARTITION or
ALTER TABLE ... TRUNCATE PARTITION is run
against a partitioned table that uses
MyISAM (or another storage engine
that makes use of table-level locking), only those partitions
that are actually read from are locked. (This does not apply to
partitioned tables using a storage enginethat employs row-level
locking, such as InnoDB.) See
Section 22.6.4, “Partitioning and Locking”.

It is possible for an ALTER TABLE
statement to contain a PARTITION BY or
REMOVE PARTITIONING clause in an addition to
other alter specifications, but the PARTITION
BY or REMOVE PARTITIONING clause
must be specified last after any other specifications.

The ADD PARTITION, DROP
PARTITION, COALESCE PARTITION,
REORGANIZE PARTITION, ANALYZE
PARTITION, CHECK PARTITION, and
REPAIR PARTITION options cannot be combined
with other alter specifications in a single ALTER
TABLE, since the options just listed act on individual
partitions. For more information, see
Section 13.1.8.1, “ALTER TABLE Partition Operations”.

Only a single instance of any one of the following options can
be used in a given ALTER TABLE
statement: PARTITION BY, ADD
PARTITION, DROP PARTITION,
TRUNCATE PARTITION, EXCHANGE
PARTITION, REORGANIZE PARTITION, or
COALESCE PARTITION, ANALYZE
PARTITION, CHECK PARTITION,
OPTIMIZE PARTITION, REBUILD
PARTITION, REMOVE PARTITIONING.

In the first case, you can analyze partitions
p1 and p2 of table
t1 concurrently using a single statement with
a single ANALYZE PARTITION option that lists
both of the partitions to be analyzed, like this:

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

In the second case, it is not possible to perform
ANALYZE and CHECK
operations on different partitions of the same table
concurrently. Instead, you must issue two separate statements,
like this:

User comments in this section are, as the name implies, provided by MySQL users.
The MySQL documentation team is not responsible for, nor do they endorse, any of
the information provided here.

Posted by
Youssef Benhssaien
on
April 11, 2017

People working on a big database (millions of lines) should pay attention to executing REORGANIZE statement, it may take hours.

The fact that when executing an ALTER TABLE with REORGANIZE partitions, MySQL creates a temporary table to store the newly organised data, that mean a combination between a CREATE (to create the new table) and SELECT (to select the old data) and INSERT (to put them into the new temporary table).