Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 32.9Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.8Kb
Man Pages (Zip) - 189.4Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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

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 Clause In Place? Permits Concurrent DML? Notes
PARTITION BY No No Permits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITION Yes* 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 PARTITION Yes* 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 PARTITION No No Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION No No Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION Yes Yes Does 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 PARTITION Yes* No ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
REORGANIZE PARTITION Yes* No ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
EXCHANGE PARTITION Yes Yes
ANALYZE PARTITION Yes Yes
CHECK PARTITION Yes Yes
OPTIMIZE PARTITION No No ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION Yes* No ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE} is supported.
REPAIR PARTITION Yes Yes
REMOVE PARTITIONING No No Permits 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 22, Partitioning.


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