MENU


Management

Altering RANGE or LIST partitions
ALTER TABLE tbl TRUNCATE PARTITION p3;
ALTER TABLE tbl DROP PARTITION p4;
ALTER TABLE tbl
ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010)); -- the end only
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1960),
PARTITION n1 VALUES LESS THAN (1970)
); -- splitting
ALTER TABLE members REORGANIZE PARTITION s2,p3 INTO (
PARTITION p0 VALUES LESS THAN (1980)
);  -- merging
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
); -- merging and splitting
ALTER TABLE tbl
PARTITION BY HASH(YEAR(dob))
PARTITIONS 8;  -- changing partitioning type


Altering HASH or KEY partitions
ALTER TABLE tbl COALESCE PARTITION 4; -- reduce by 4
ALTER TABLE tbl ADD PARTITION PARTITIONS 6; -- add 6


Others
ALTER TABLE pt  -- partitioned tabled
EXCHANGE PARTITION p
WITH TABLE tbl;  -- non-partitioned table
# exchanges all the rows in pt with the rows in tbl
ALTER TABLE e2 REMOVE PARTITIONING;
# removes partitioning
ALTER TABLE tbl REBUILD PARTITION p0, p1;
# removes and reinserts all rows
ALTER TABLE tbl OPTIMIZE PARTITION p0, p1;
# same as running CHECK PARTITION,
# ANALYZE PARTITION, REPAIR PARTITION
ALTER TABLE tbl ANALYZE PARTITION p3;
# reads and stores key distributions
ALTER TABLE t1 REPAIR PARTITION p0,p1;
# repairs corrupted partitions
ALTER TABLE trb3 CHECK PARTITION p1;
# like CHECK TABLE
ALTER TABLE tbl TRUNCATE PARTITION ALL;
# empties all partitions
SHOW CREATE TABLE
SHOW CREATE STATUS
EXPLAIN PARTITIONS SELECT * FROM tbl