1

My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own?

Let's assume I have part0, part1, and part2. Is there any ALTER TABLE that I can give that converts part0 to a new table?

My thought process was, since the data is already existing in a separate file on disk, the table creation process may be faster than creating a new table using a SELECT query.

2
0

You do not need SELECT. This can be done with pure DDL.

For this example, let say the partitioned table is as follows:

  1. Database mydb
  2. Table mytable
  3. Partitions (part0,part1,part2)

This can be tricky but here is goes

USE mydb
CREATE TABLE mynewtable LIKE mytable;
ALTER TABLE mynewtable REMOVE PARTITIONING; 
ALTER TABLE mytable
    EXCHANGE PARTITION p0
    WITH TABLE mynewtable
    WITHOUT VALIDATION
;
ALTER TABLE mytable DROP PARTITION p0;

What this does is first make mynewtable without any partitions and the same schema in all other aspects. Then, it swaps the mynewtable table with part0.

If you are not sure of this, then run

USE mydb
CREATE TABLE mynewtable LIKE mytable;
ALTER TABLE mynewtable REMOVE PARTITIONING; 
ALTER TABLE mytable
    EXCHANGE PARTITION p0
    WITH TABLE mynewtable
    WITHOUT VALIDATION
;

Examine the rows in mynewtable and the new part0 and make sure

  • mynewtable has the data from the original part0
  • The new part0 should be empty

Once you are sure the transposition worked then run

ALTER TABLE mytable DROP PARTITION p0;

Please test this in a lab or sqlfiddle. GIVE IT A TRY !!!

0

Yes, a PARTITION is a TABLE. However, it takes some special commands to turn a partition into a table or vice versa.

See "transportable tablespaces" and FLUSH TABLES ptable FOR EXPORT; ALTER TABLE ptable DISCARD TABLESPACE; ALTER TABLE ptable IMPORT TABLESPACE. The details are different between MySQL 5.6 and 5.7+.

http://mysql.rjweb.org/doc.php/partitionmaint#use_cases_for_partitioning has links to a few places that discuss the techniques.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.