ALTER TABLE
Partition Operations
Partitioning-related clauses for ALTER TABLE
can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance.
- Simply using a
partition_options
clause withALTER TABLE
on a partitioned table repartitions the table according to the partitioning scheme defined by thepartition_options
. This clause always begins withPARTITION BY
, and follows the same syntax and other rules as apply to thepartition_options
clause forCREATE TABLE
(see , "CREATE TABLE
Syntax", for more detailed information), 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 theid
column as the partitioning key, into 8 partitions by means of this statement:ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8;
The table that results from using an
ALTER TABLE ... PARTITION BY
statement must follow the same rules as one created usingCREATE 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 , "Partitioning Keys, Primary Keys, and Unique Keys". TheCREATE TABLE ... PARTITION BY
rules for specifying the number of partitions also apply toALTER TABLE ... PARTITION BY
.The
partition_definition
clause forALTER TABLE ADD PARTITION
supports the same options as the clause of the same name for theCREATE TABLE
statement. (See , "CREATE TABLE
Syntax", for the syntax and description.) Suppose that you have the partitioned table created as shown here:CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) );
You can add a new partition
p3
to this table for storing values less than2002
as follows:ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
DROP PARTITION
can be used to drop one or moreRANGE
orLIST
partitions. This statement cannot be used withHASH
orKEY
partitions; instead, useCOALESCE PARTITION
(see below). Any data that was stored in the dropped partitions named in thepartition_names
list is discarded. For example, given the tablet1
defined previously, you can drop the partitions namedp0
andp1
as shown here:ALTER TABLE t1 DROP PARTITION p0, p1;
ADD PARTITION
andDROP PARTITION
do not currently supportIF [NOT] EXISTS
. It is also not possible to rename a partition or a partitioned table. Instead, if you wish to rename a partition, you must drop and re-create the partition; if you wish to rename a partitioned table, you must instead drop all partitions, rename the table, and then add back the partitions that were dropped.In MariaDB 5.6, it is possible to delete rows from selected partitions using the
TRUNCATE PARTITION
option. This option takes a comma-separated list of one or more partition names. For example, consider the tablet1
as defined here:CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2003), PARTITION p4 VALUES LESS THAN (2007) );
To delete all rows from partition
p0
, you can use the following statement:ALTER TABLE t1 TRUNCATE PARTITION p0;
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 withDELETE
statements. For example, this statement deletes all rows from partitionsp1
andp3
:ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
An equivalent
DELETE
statement is shown here:DELETE FROM t1 WHERE (year_col >= 1991 AND year_col < 1995) OR (year_col >= 2003 AND year_col < 2007);
You can also use the
ALL
keyword in place of the list of partition names; in this case, the statement acts on all partitions in the table.TRUNCATE PARTITION
merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.NoteTRUNCATE PARTITION
does not work with subpartitions.You can verify that the rows were dropped by checking the
INFORMATION_SCHEMA.PARTITIONS
table, using a query such as this one:SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1';
TRUNCATE PARTITION
is supported only for partitioned tables that use theMyISAM
,InnoDB
, orMEMORY
storage engine. It also works onBLACKHOLE
tables (but has no effect). It is not supported forARCHIVE
tables.COALESCE PARTITION
can be used with a table that is partitioned byHASH
orKEY
to reduce the number of partitions bynumber
. Suppose that you have created tablet2
using the following definition:CREATE TABLE t2 ( name VARCHAR (30), started DATE ) PARTITION BY HASH( YEAR(started) ) PARTITIONS 6;
You can reduce the number of partitions used by
t2
from 6 to 4 using 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 can be done by naming several partitions in the
partition_names
list and supplying a single definition forpartition_definition
. - To split an existing partition into several partitions. You can accomplish this by naming a single partition for
partition_names
and providing multiplepartition_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 usingVALUES IN
.
For partitions that have not been explicitly named, MariaDB automatically provides the default names
p0
,p1
,p2
, and so on. The same is true with regard to subpartitions.For more detailed information about and examples of
ALTER TABLE ... REORGANIZE PARTITION
statements, see , "Management ofRANGE
andLIST
Partitions". - To merge a set of partitions into a single partition. This can be done by naming several partitions in the
- It is also possible in MariaDB 5.6 to exchange a table partition or subpartition with a table using
ALTER TABLE
, wherept
EXCHANGE PARTITIONp
WITH TABLEnt
pt
is the partitioned table andp
is the partition or subpartition ofpt
to be exchanged with unpartitioned tablent
, provided that the following statements are true:
- Table
nt
is not itself partitioned. - The structures of tables
pt
andnt
are otherwise identical. - There are no rows in
nt
that lie outside the boundaries of the partition definition forp
. - Table
nt
contains no foreign key references, and no other table has any foreign keys that refer tont
.
Executing
ALTER TABLE ... EXCHANGE PARTITION
does not invoke any triggers on either the partitioned table or the table to be exchanged.Any
AUTO_INCREMENT
columns in the table to be exchanged with a partition are reset.The
IGNORE
keyword has no effect when used withALTER TABLE ... EXCHANGE PARTITION
.For more information about and examples of
ALTER TABLE ... EXCHANGE PARTITION
, see , "Exchanging Partitions and Subpartitions with Tables". - Table
- Several additional options provide partition maintenance and repair functionality analogous to that implemented for nonpartitioned tables by statements such as
CHECK TABLE
andREPAIR TABLE
(which are also supported for partitioned tables; see , "Table Maintenance Statements" for more information). These includeANALYZE PARTITION
,CHECK PARTITION
,OPTIMIZE PARTITION
,REBUILD PARTITION
, andREPAIR PARTITION
. Each of these options takes apartition_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 theALL
keyword in place ofpartition_names
, in which case the statement acts on all partitions in the table. For more information and examples, see , "Maintenance of Partitions".
The
ANALYZE PARTITION
,CHECK PARTITION
,OPTIMIZE PARTITION
, andREPAIR 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 otherALTER TABLE
options such as those used to add, drop, or rename drop columns or indexes.- Using the
ENGINE
option withALTER TABLE
changes the storage engine used by the table without affecting the partitioning.
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
, REORGANIZE PARTITION
, or COALESCE PARTITION
, ANALYZE PARTITION
, CHECK PARTITION
, OPTIMIZE PARTITION
, REBUILD PARTITION
, REMOVE PARTITIONING
.
For example, the following two statements are invalid:
ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2; ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;
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: