reorganize syntax / procedure
Hi all
Looking for some guidance on REORGANIZE PARTITION.
I have a partitioned table as follows.
CREATE TABLE `ap_log` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`time` bigint(10) unsigned NOT NULL DEFAULT '0',
`recno` bigint(10) unsigned NOT NULL DEFAULT '0',
...Other stuff...
) ENGINE=InnoDB AUTO_INCREMENT=96924460 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (time)
SUBPARTITION BY KEY (recno)
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (1238558400) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1246420800) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1254369600) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1262322000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (1270094400) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (1277956800) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (1285905600) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (1293858000) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (1301630400) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (1309492800) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (1317441600) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (1325394000) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
I need to add more partitions .. and reorganize p12 which is being filled now.
I have come up with the following steps
step 1) reorganize p12 data into a p12t with the following syntax
mysql> ALTER TABLE ap_log REORGANIZE PARTITION p12 INTO (
PARTITION p12t VALUES LESS THAN (1333252800) ENGINE = InnoDB);
step 2) add remaining tables with the following syntax
mysql> ALTER TABLE ap_log ADD PARTITION (
PARTITION p13 VALUES LESS THAN (1341115200) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (1349064000) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (1357016400) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (1364788800) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (1372651200) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (1380600000) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (1388552400) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
I think all that will work.. and give me the results I want.. with the data.
I do have a question on the syntax and how the SUBPARTITIONS will be handled.
When I reorg p12 will the subpartitions remain intact?
When I add the newer tables will the subpartition definition of the table follow through or do I need to explicitly state that again in my ADD statement.
thanks for any help/pointers
kevin