How to drop a partition efficiently with exchange partition instead of drop partition?
Last updated on MARCH 12, 2017
Applies to:MySQL Server - Version 5.6 and later
Information in this document applies to any platform.
Exchanging Partition could be helpful to avoid table-level metadata locking when you drop a partition.
If partitioned table has been containing historical data by range, it will be managed periodically, for example, old and hardly accessed partitions need to be dropped.
When issuing ALTER TABLE ... DROP PARTITION, statement will take metadata locks on the table level (even it takes few seconds), it could be a critical problem for particular production services.
In MySQL 5.6, it is possible to exchange a table partition or sub-partition with a table using the ALTER TABLE ... EXCHANGE PARTITION statement.
Exchange partition can be used as an alternative to dropping partition. Let's see the example.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms