79508

Implementation Details of Online DDL

Each alter table operation for an innoDE table is governed by several aspects:

•    Whether there is any change to the physical representation of the table, or whether it purely a

change to metadata that can be done without touching the table itself.

•    Whether the volume of data in the table stays the same, increases, or decreases.

•    Whether a change in table data involves the clustered index, secondary indexes, or both.

•    Whether there are any foreign key relationships between the table being altered and some other

table. The mechanics differ depending on whether the foreign_key_checks [497] configuration option is enabled or disabled.

•    Whether the table is partitioned. Partitioning clauses of ALTER TABLE are turned into low-level

operations involving one or morę tables, and those operations follow the regular rules for online DDL.

•    Whether the table data must be copied, whether the table can be reorganized “in-place”, or a

combination of both.

•    Whether the table contains any auto-increment columns.

•    What degree of locking is required, either by the naturę of the underlying database operations, or a

lock clause that you specify in the alter table statement.

This section explains how these factors affect the different kinds of alter table operations on InnoDB tables.