My Oracle Support Banner

How to drop a partition efficiently with exchange partition instead of drop partition? (Doc ID 2052872.1)

Last updated on MARCH 26, 2020

Applies to:

MySQL Server - Version 5.5 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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 1. Prerequisite
 2. Compare with Drop Partition & Exchange Partition
 2-1.  Drop Partition
 2-2. Exchange partition

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.