Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 214.6Kb
Man Pages (Zip) - 327.6Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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

14.13.7 Online DDL for Partitioned Tables

Most 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 when 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 14.11 Online Status for ALTER TABLE Partitioning Clauses

Partitioning ClauseIn-Place?Permits Concurrent DML?Notes
PARTITION BYNoNoPermits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.
DROP PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST.
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 PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
REORGANIZE PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
EXCHANGE PARTITIONYesYes 
ANALYZE PARTITIONYesYes 
CHECK PARTITIONYesYes 
OPTIMIZE PARTITIONNoNoALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITIONNoNoOnly permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
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 22, Partitioning.


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