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

Last updated on MARCH 12, 2017

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Goal

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.

 

Solution

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