ALTER TABLE tbl REORGANIZE PARTITION p_50000000 INTO
(
PARTITION p_50000000 VALUES LESS THAN (50000000),
PARTITION p_2008_12 VALUES LESS THAN (2307392000),
PARTITION p_2009_01 VALUES LESS THAN (2334176000),
PARTITION p_2009_02 VALUES LESS THAN (2358368000),
PARTITION p_2009_03 VALUES LESS THAN (2385152000),
PARTITION p_2009_04 VALUES LESS THAN (2411072000),
PARTITION p_2009_05 VALUES LESS THAN (2437856000)
);
我从分区p_50000000中划分出这么多分区,现在后悔了,想将这些分区合并回p_50000000!如何做到?
解决方案 »
- Cannot create PoolableConnectionFactory
- 如何在我的VC程序中列出PostgreSQL的数据表?
- 我在导入数据的时候,mysql -uroot -proyasoft mccdb <d:/mccdb/mccdb_0519.sql,出现以下信息!
- MySQL实现双向同步,但其中的一台机器需要经常的重起,关闭这该如何办呢?
- 关于MySQL的外键问题
- Mysql4.x版在Windows平台下的数据库同步实现
- 在linux9.0下mysql的启动问题
- MySql 5.6.16版本32位默认占用内存多大?
- mysql前台数据显示的问题-----
- 求助!!!create database 显示 not connected!
- 求sql语句,sqlite数据库
- 查看本机的sql数据库中的表的内容
alter table tb_key_part11 reorganize partition p2,p3 into
(partition p3 values less than (30)
)
ALTER TABLE tbl REORGANIZE PARTITION p_50000000,p_2008_12,p_2009_01,p_2009_02,p_2009_03,p_2009_04,p_2009_05 INTO
(
PARTITION p_2009_05 VALUES LESS THAN (2437856000)
);
ALTER TABLE tbl REORGANIZE PARTITION p_2009_05 into
(
PARTITION p_2009_05 VALUES LESS THAN (2437855000),
PARTITION p_50000000 VALUES LESS THAN (2437856000)
);ALTER TABLE tbl REORGANIZE PARTITION p_2009_05,p_50000000 into
(
PARTITION p_50000000 VALUES LESS THAN (2437856000)
);
PARTITION p0 VALUES LESS THAN (1970)
);合并两个分区到一个。
18.3.3. 分区维护重建分区: 这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。
ALTER TABLE t1 REBUILD PARTITION (p0, p1);优化分区:如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1);分析分区:读取并保存分区的键分布。
ALTER TABLE t1 ANALYZE PARTITION (p3);修补分区: 修补被破坏的分区。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);检查分区: 可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
ALTER TABLE trb3 CHECK PARTITION (p1);
mysql> CREATE TABLE `members` (
-> `id` int(11) default NULL,
-> `fname` varchar(25) default NULL,
-> `lname` varchar(25) default NULL,
-> `dob` date default NULL
-> ) ENGINE=MyISAM
-> PARTITION BY RANGE ( YEAR(dob) ) (
-> PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
-> PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
-> PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM,
-> PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
-> );
Query OK, 0 rows affected (0.22 sec)mysql> insert into members values (1,'a','a','1969-02-02');
Query OK, 1 row affected (0.05 sec)mysql> insert into members values (2,'a','a','1979-02-02');
Query OK, 1 row affected (0.00 sec)mysql> insert into members values (3,'a','a','1989-02-02');
Query OK, 1 row affected (0.00 sec)mysql> insert into members values (4,'a','a','1999-02-02');
Query OK, 1 row affected (0.00 sec)mysql> select * from members;
+------+-------+-------+------------+
| id | fname | lname | dob |
+------+-------+-------+------------+
| 1 | a | a | 1969-02-02 |
| 2 | a | a | 1979-02-02 |
| 3 | a | a | 1989-02-02 |
| 4 | a | a | 1999-02-02 |
+------+-------+-------+------------+
4 rows in set (0.01 sec)mysql>
mysql>
mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO (
-> PARTITION s0 VALUES LESS THAN (1960),
-> PARTITION s1 VALUES LESS THAN (1970)
-> );
Query OK, 1 row affected (0.38 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> show create table members;
+---------+---------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------
| members | CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION s0 VALUES LESS THAN (1960) ENGINE = MyISAM,
PARTITION s1 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM) */ |
+---------+---------------------------------------------------------------------
1 row in set (0.02 sec)mysql> select * from members;
+------+-------+-------+------------+
| id | fname | lname | dob |
+------+-------+-------+------------+
| 1 | a | a | 1969-02-02 |
| 2 | a | a | 1979-02-02 |
| 3 | a | a | 1989-02-02 |
| 4 | a | a | 1999-02-02 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)mysql>
mysql> ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
-> PARTITION p0 VALUES LESS THAN (1970)
-> );
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> show create table members;
+---------+---------------------------------------------------------------------
| Table | Create Table
+---------+---------------------------------------------------------------------
| members | CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM) */ |
+---------+---------------------------------------------------------------------
1 row in set (0.02 sec)mysql> select * from members;
+------+-------+-------+------------+
| id | fname | lname | dob |
+------+-------+-------+------------+
| 1 | a | a | 1969-02-02 |
| 2 | a | a | 1979-02-02 |
| 3 | a | a | 1989-02-02 |
| 4 | a | a | 1999-02-02 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)mysql>