MySQL Forums
Forum List  »  Partitioning

reorganize syntax / procedure
Posted by: kevin foote
Date: January 12, 2012 03:36PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
reorganize syntax / procedure
4361
January 12, 2012 03:36PM
5092
January 13, 2012 10:35AM
1909
January 13, 2012 03:57PM
2448
January 13, 2012 07:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.