Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.6Mb
PDF (A4) - 34.6Mb
PDF (RPM) - 32.2Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 147.5Kb
Man Pages (Zip) - 208.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  ...  /  Online DDL for Partitioned Tables

Pre-General Availability Draft: 2017-12-13

15.12.6 Online DDL for Partitioned Tables

Some ALTER TABLE partitioning clauses do not go through the same internal online DDL API as regular non-partitioned InnoDB tables. As a result, online support for ALTER TABLE partitioning clauses varies.

The following table shows the online status for each ALTER TABLE partitioning statement. Regardless of the online DDL API that is used, MySQL attempts to minimize data copying and locking where possible.

  • The In-Place? column shows which operations permit the ALGORITHM=INPLACE clause.

  • The Permits Concurrent DML? column shows which operations are performed fully online. You can specify LOCK=NONE to assert that concurrent DML is permitted during the DDL operation. MySQL automatically permits concurrent DML where possible.

    For operations that support ALGORITHM={COPY|INPLACE}, you can specify LOCK=SHARED to assert that concurrent queries are permitted during a DDL operation. MySQL automatically permits concurrent queries where possible.

  • The Notes column provides additional information and explains exceptions and dependencies related to the Yes/No values of other columns. An asterisk indicates an exception or dependency.

ALTER TABLE partitioning options that use ALGORITHM=COPY or that only permit ALGORITHM=DEFAULT, LOCK=DEFAULT, repartition the table using the COPY algorithm. In other words, a new partitioned table is created with the new partitioning scheme. The newly created table includes any changes applied by the ALTER TABLE statement, and table data is copied into the new table structure.

Table 15.13 Online Status for ALTER TABLE Partitioning Clauses

Partitioning ClauseIn-Place?Permits Concurrent DML?Notes
PARTITION BYNoNoPermits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITIONYes*Yes*ALGORITHM=INPLACE, LOCK={DEFAULT|NONE|SHARED|EXCLUSISVE} is supported for RANGE and LIST partitions, ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSISVE} for HASH and KEY partitions, and ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE} for all partition types. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted with ALGORITHM=COPY for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
DROP PARTITIONYes*Yes*

ALGORITHM=INPLACE, LOCK={DEFAULT|NONE|SHARED|EXCLUSIVE} is supported. Does not copy data for tables partitioned by RANGE or LIST.

DROP PARTITION with ALGORITHM=INPLACE deletes data stored in the partition and drops the partition. However, DROP PARTITION with ALGORITHM=COPY or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES definition. Data that cannot be moved to another partition is deleted.

DISCARD PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITIONYesYesDoes not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITIONYes*NoALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
REORGANIZE PARTITIONYes*NoALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
EXCHANGE PARTITIONYesYes 
ANALYZE PARTITIONYesYes 
CHECK PARTITIONYesYes 
OPTIMIZE PARTITIONNoNoALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 23.3.4, “Maintenance of Partitions”.
REBUILD PARTITIONYes*NoALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
REPAIR PARTITIONYesYes 
REMOVE PARTITIONINGNoNoPermits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}

Non-partitioning online ALTER TABLE operations on partitioned tables follow the same rules that apply to regular tables. However, ALTER TABLE performs online operations on each table partition, which causes increased demand on system resources due to operations being performed on multiple partitions.

For additional information about ALTER TABLE partitioning clauses, see Partitioning Options, and Section 13.1.8.1, “ALTER TABLE Partition Operations”. For information about partitioning in general, see Chapter 23, Partitioning.


User Comments
Sign Up Login You must be logged in to post a comment.