MySQL Forums
Forum List  »  Partitioning

Online Partition addition use "add partition" clause or "reorganize" clause?
Posted by: Tom Song
Date: October 21, 2014 03:53AM

MySQL version is 5.6.13

This is table script:

CREATE TABLE `user_calllog` (
`FID` int(11) NOT NULL AUTO_INCREMENT,
`FCALLCATEGORY` varchar(32) DEFAULT NULL,
`FCREATEBY` varchar(36) DEFAULT NULL,
`FDATECREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`FDATELASTUPDATE` datetime DEFAULT NULL,
`FCALLTYPE` varchar(32) DEFAULT NULL,
`FSTATUS` char(1) DEFAULT NULL,
`FTITLE` varchar(32) DEFAULT NULL,
`FATTACHEDFILE` varchar(64) DEFAULT NULL,
`FDESC` varchar(1000) DEFAULT NULL,
`FUSERID` varchar(255) DEFAULT NULL,
`FWEBCODE` varchar(7) DEFAULT '',
PRIMARY KEY (`FID`,`FDATECREATED`),
KEY `i_user_calllog_fuserid` (`FUSERID`),
KEY `idx_user_calllog_fdatecreated` (`FDATECREATED`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (FDATECREATED)
(
PARTITION p_201406 VALUES LESS THAN ('2014-07-01'),
PARTITION p_201407 VALUES LESS THAN ('2014-08-01'),
PARTITION p_201408 VALUES LESS THAN ('2014-09-01'),
PARTITION p_201409 VALUES LESS THAN ('2014-10-01'),
PARTITION p_201410 VALUES LESS THAN ('2014-11-01'),
PARTITION p_201411 VALUES LESS THAN ('2014-12-01'),
PARTITION p_MAX VALUES LESS THAN (MAXVALUE)
);

2 choices:

1. "add partition" clause

--Can confirm that p_MAX partition no record

ALTER TABLE user_calllog DROP PARTITION p_MAX;
alter table user_calllog add PARTITION (partition p_201412 VALUES LESS THAN ('2015-01-01'));

2. "reorganize" clause
ALTER TABLE user_calllog REORGANIZE PARTITION p_MAX INTO (
PARTITION p_201412 VALUES LESS THAN ('2015-01-01'),
PARTITION p_MAX VALUES LESS THAN (MAXVALUE)
);

Please help advise which one is ok for online ddl operation, or both ok? thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Online Partition addition use "add partition" clause or "reorganize" clause?
3096
October 21, 2014 03:53AM


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.