	

使用ALTER TABLE .. MERGE PARTITION语句将两个分区的内容合并到另外一个分区，两个源分区和关联的local index都会被drop

	　　不能用于 hash分区表或 hash subpartitions of a composite *-hash partitioned table

	　　不能用于合并引用分区表(reference-partitioned table)

	合并范围分区

	 一次只能合并两个相邻的分区

	　　允许合并两个临近范围的分区到另外分区，不相邻分区无法合并。合并结果分区继承两个源分区的最大边界。

	ALTER TABLE four_seasons MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two UPDATE INDEXES;

	如果不显式声明 UPDATE INDEXES 语句,就必须给受影响的分区 rebuild the local index.

	ALTER TABLE four_seasons MODIFY PARTITION 
quarter_two REBUILD UNUSABLE LOCAL INDEXES;

实验操作脚本

--清理环境
drop table vast.hepart;
--创建一个分区表
CREATE TABLE vast.hepart
( prod_id NUMBER(6) primary key,
xname varchar2(20),
lname varchar2(20),
time_id DATE)
PARTITION BY RANGE (time_id)
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')));
--创建索引
--在建表是已经有一个唯一的主键索引，并且是global的
--创建一个一般索引
create index vast.idn_hepart_xname on vast.hepart(xname);
--创建一个本地索引（分区索引）
create index vast.idn_hepart_lname on vast.hepart(lname) local;
--插入测试数据，一个分区一条
insert into vast.hepart values (1,'a','a',to_date('2007-05-05','yyyy-mm-dd'));
insert into vast.hepart values (2,'b','b',to_date('2008-05-06','yyyy-mm-dd'));
insert into vast.hepart values (3,'c','c',to_date('2009-02-05','yyyy-mm-dd'));
insert into vast.hepart values (4,'d','d',to_date('2009-12-15','yyyy-mm-dd'));
commit;
--按分区查数据
select * from vast.hepart partition(p0);
select * from vast.hepart partition(p1);
select * from vast.hepart partition(p2);
select * from vast.hepart partition(p3);
--查看此时索引状态
select i.status,i.* from dba_indexes i where i.table_name='HEPART';
select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
select pi.* from dba_part_indexes pi where pi.table_name='HEPART';
--新增三个分区
alter table vast.hepart
add partition p4 values less than (TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
alter table vast.hepart
add partition p5 values less than (TO_DATE(' 2030-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
alter table vast.hepart
add partition p6 values less than (TO_DATE(' 2050-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
select * from dba_tab_partitions p where p.table_name='HEPART';
--像新分区插入数据
insert into vast.hepart values (5,'e','e',to_date('2010-05-05','yyyy-mm-dd'));
insert into vast.hepart values (6,'f','f',to_date('2022-05-06','yyyy-mm-dd'));
insert into vast.hepart values (7,'g','g',to_date('2038-02-05','yyyy-mm-dd'));
commit;
--查看新分区的记录
select * from vast.hepart partition(p4);
select * from vast.hepart partition(p5);
select * from vast.hepart partition(p6);
--开始合并p4 p5分区
ALTER TABLE vast.hepart MERGE PARTITIONS p4,p5 INTO PARTITION p5 ;
select * from dba_tab_partitions p where p.table_name='HEPART';
select * from vast.hepart partition(p5);
select i.status,i.* from dba_indexes i where i.table_name='HEPART';
select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
select pi.* from dba_part_indexes pi where pi.table_name='HEPART';
--此时索引失效，本地索引对应分区索引失效，故要加上update indexes
ALTER TABLE vast.hepart MERGE PARTITIONS p5,p6 INTO PARTITION p6 update indexes;
select * from dba_tab_partitions p where p.table_name='HEPART';
select * from vast.hepart partition(p6);
select i.status,i.* from dba_indexes i where i.table_name='HEPART';
select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
select pi.* from dba_part_indexes pi where pi.table_name='HEPART';

ITPub博客

合并分区表分区

最新文章