使用 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博客 ” ,链接:http://blog.itpub.net/30820196/viewspace-2141402/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30820196/viewspace-2141402/