MySQL Forums :: Partitioning :: Online Partition addition use "add partition" clause or "reorganize" clause?


Advanced Search

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? 2097 Tom Song 10/21/2014 03:53AM
Re: Online Partition addition use "add partition" clause or "reorganize" clause? 979 Tom Song 10/22/2014 11:58PM
Re: Online Partition addition use "add partition" clause or "reorganize" clause? 936 Rick James 10/23/2014 04:36PM
Re: Online Partition addition use "add partition" clause or "reorganize" clause? 955 Tom Song 10/24/2014 02:05AM
Re: Online Partition addition use "add partition" clause or "reorganize" clause? 906 Mikael Ronström 11/13/2014 06:54AM


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.