19.3.1 Management of RANGE and LIST Partitions

Range and list partitions are very similar with regard to how
the adding and dropping of partitions are handled. For this
reason we discuss the management of both sorts of partitioning
in this section. For information about working with tables that
are partitioned by hash or key, see
Section 19.3.2, “Management of HASH and KEY Partitions”. Dropping a
RANGE or LIST partition is
more straightforward than adding one, so we discuss this first.

Dropping a partition from a table that is partitioned by either
RANGE or by LIST can be
accomplished using the
ALTER
TABLE statement with a DROP
PARTITION clause. Here is a very basic example, which
supposes that you have already created a table which is
partitioned by range and then populated with 10 records using
the following CREATE TABLE and
INSERT statements:

The NDBCLUSTER storage engine
does not support ALTER TABLE ... DROP
PARTITION. It does, however, support the other
partitioning-related extensions to
ALTER
TABLE that are described in this chapter.

It is very important to remember that, when you drop a
partition, you also delete all the data that was stored in that
partition. You can see that this is the case by
re-running the previous SELECT
query:

mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

Because of this, you must have the
DROP privilege for a table before
you can execute ALTER TABLE ... DROP
PARTITION on that table.

If you intend to change the partitioning of a table
without losing data, use ALTER
TABLE ... REORGANIZE PARTITION instead. See below or
in Section 13.1.7, “ALTER TABLE Syntax”, for information about
REORGANIZE PARTITION.

If you now execute a SHOW CREATE
TABLE statement, you can see how the partitioning
makeup of the table has been changed:

Note that the number of rows dropped from the table as a result
of ALTER TABLE ... DROP PARTITION is not
reported by the server as it would be by the equivalent
DELETE query.

Dropping LIST partitions uses exactly the
same ALTER TABLE ... DROP PARTITION syntax as
used for dropping RANGE partitions. However,
there is one important difference in the effect this has on your
use of the table afterward: You can no longer insert into the
table any rows having any of the values that were included in
the value list defining the deleted partition. (See
Section 19.2.2, “LIST Partitioning”, for an example.)

To add a new range or list partition to a previously partitioned
table, use the ALTER TABLE ... ADD PARTITION
statement. For tables which are partitioned by
RANGE, this can be used to add a new range to
the end of the list of existing partitions. Suppose that you
have a partitioned table containing membership data for your
organization, which is defined as follows:

Suppose further that the minimum age for members is 16. As the
calendar approaches the end of 2005, you realize that you will
soon be admitting members who were born in 1990 (and later in
years to come). You can modify the members
table to accommodate new members born in the years 1990 to 1999
as shown here:

With tables that are partitioned by range, you can use
ADD PARTITION to add new partitions to the
high end of the partitions list only. Trying to add a new
partition in this manner between or before existing partitions
results in an error as shown here:

mysql> ALTER TABLE members
> ADD PARTITION (
> PARTITION n VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition

You can work around this problem by reorganizing the first
partition into two new ones that split the range between them,
like this:

Because any rows with the data column value
12 have already been assigned to partition
p1, you cannot create a new partition on
table tt that includes 12
in its value list. To accomplish this, you could drop
p1, and add np and then a
new p1 with a modified definition. However,
as discussed earlier, this would result in the loss of all data
stored in p1—and it is often the case
that this is not what you really want to do. Another solution
might appear to be to make a copy of the table with the new
partitioning and to copy the data into it using
CREATE TABLE ...
SELECT ..., then drop the old table and rename the new
one, but this could be very time-consuming when dealing with a
large amounts of data. This also might not be feasible in
situations where high availability is a requirement.

You can add multiple partitions in a single ALTER TABLE
... ADD PARTITION statement as shown here:

Fortunately, MySQL's partitioning implementation provides ways
to redefine partitions without losing data. Let us look first at
a couple of simple examples involving RANGE
partitioning. Recall the members table which
is now defined as shown here:

Suppose that you would like to move all rows representing
members born before 1960 into a separate partition. As we have
already seen, this cannot be done using
ALTER
TABLE ... ADD PARTITION. However, you can use another
partition-related extension to
ALTER
TABLE to accomplish this:

In effect, this command splits partition p0
into two new partitions s0 and
s1. It also moves the data that was stored in
p0 into the new partitions according to the
rules embodied in the two PARTITION ... VALUES
... clauses, so that s0 contains
only those records for which
YEAR(dob) is less than 1960 and
s1 contains those rows in which
YEAR(dob) is greater than or
equal to 1960 but less than 1970.

A REORGANIZE PARTITION clause may also be
used for merging adjacent partitions. You can return the
members table to its previous partitioning as
shown here:

No data is lost in splitting or merging partitions using
REORGANIZE PARTITION. In executing the above
statement, MySQL moves all of the records that were stored in
partitions s0 and s1 into
partition p0.

Here, tbl_name is the name of the
partitioned table, and partition_list
is a comma-separated list of names of one or more existing
partitions to be changed.
partition_definitions is a
comma-separated list of new partition definitions, which follow
the same rules as for the
partition_definitions list used in
CREATE TABLE (see
Section 13.1.17, “CREATE TABLE Syntax”). It should be noted that you are
not limited to merging several partitions into one, or to
splitting one partition into many, when using
REORGANIZE PARTITION. For example, you can
reorganize all four partitions of the members
table into two, as follows:

You can also use REORGANIZE PARTITION with
tables that are partitioned by LIST. Let us
return to the problem of adding a new partition to the
list-partitioned tt table and failing because
the new partition had a value that was already present in the
value-list of one of the existing partitions. We can handle this
by adding a partition that contains only nonconflicting values,
and then reorganizing the new partition and the existing one so
that the value which was stored in the existing one is now moved
to the new one:

Here are some key points to keep in mind when using
ALTER TABLE ... REORGANIZE PARTITION to
repartition tables that are partitioned by
RANGE or LIST:

The PARTITION clauses used to determine
the new partitioning scheme are subject to the same rules as
those used with a CREATE
TABLE statement.

Most importantly, you should remember that the new
partitioning scheme cannot have any overlapping ranges
(applies to tables partitioned by RANGE)
or sets of values (when reorganizing tables partitioned by
LIST).

The combination of partitions in the
partition_definitions list should
account for the same range or set of values overall as the
combined partitions named in the
partition_list.

For instance, in the members table used
as an example in this section, partitions
p1 and p2 together
cover the years 1980 through 1999. Therefore, any
reorganization of these two partitions should cover the same
range of years overall.

For tables partitioned by RANGE, you can
reorganize only adjacent partitions; you cannot skip over
range partitions.

For instance, you could not reorganize the
members table used as an example in this
section using a statement beginning with ALTER
TABLE members REORGANIZE PARTITION p0,p2 INTO ...
because p0 covers the years prior to 1970
and p2 the years from 1990 through 1999
inclusive, and thus the two are not adjacent partitions.

You cannot use REORGANIZE PARTITION to
change the table's partitioning type; that is, you cannot
(for example) change RANGE partitions to
HASH partitions or vice
versa. You also cannot use this command to
change the partitioning expression or column. To accomplish
either of these tasks without dropping and re-creating the
table, you can use
ALTER
TABLE ... PARTITION BY .... For example: