融合（merge partition）分区

融合（merge partition）分区：你可以融合范围分区表和list分区表的两个分区到一个分区，两个原始的分区会被删除，这个时候ROWID也就改变了，当然也包括删除相关的本地索

引。
限制：1、不能为hash分区使用这个语法，因为hash分区已经有了Coalescing Partitions分区的语法。
      2、当融合范围分区的时候必须是相邻的分区,否则报错ORA-14274: 要合并的分区不相邻，但是list分区并没有这个限制。
      如果融合的时候
      3、如果合并范围分区表的分区和maxvalue分区，那合并后的还是maxvalue分区。
      4、如果何必list分区的分区和default分区，那合并后的还是default分区。
      5、如果合并的分区中有数据,那全局索引和普通索引均失效，除非带上update indexes
      6、如果合并的分区中有数据，那本地索引关于合并后新分区的索引会失效，除非带上update indexes
语法：
ALTER TABLE q1_sales_by_region
   MERGE PARTITIONS q1_northcentral, q1_southcentral
   INTO PARTITION q1_central
      {STORAGE(MAXEXTENTS 20)
      UPDATE INDEXES};

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

使用脚本：
drop table t_pe_l;
drop table t_pe_r;

CREATE TABLE t_pe_r (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20),
         PARTITION P3 VALUES LESS THAN (30),
         PARTITION P4 VALUES LESS THAN (40),
         PARTITION p5 VALUES LESS THAN (maxvalue));

create index t_pe_r_n on t_pe_r(i);
create index t_pe_r_l on t_pe_r(j) local;
create index t_pe_r_g on t_pe_r(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_l (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY list(j)
        (PARTITION p1 VALUES (10),
         PARTITION p2 VALUES (20),
         PARTITION P3 VALUES (30),
         PARTITION P4 VALUES (40),
         PARTITION p5 VALUES (default));

create index t_pe_l_n on t_pe_l(i);
create index t_pe_l_l on t_pe_l(j) local;
create index t_pe_l_g on t_pe_l(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

insert into t_pe_l
values(2,10,5,'A');
insert into t_pe_l
values(1,20,10,'B');
insert into t_pe_l
values(3,30,40,'C');
insert into t_pe_l
values(2,40,30,'A');
insert into t_pe_l
values(1,40,20,'B');
insert into t_pe_l
values(3,85,50,'C');

insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,15,'b','B');
insert into t_pe_r
values(2,10,'a','A');
insert into t_pe_r
values(1,20,'b','B');
insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,25,'b','B');
insert into t_pe_r
values(3,85,'c','C');

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
ALTER TABLE t_pe_r
   MERGE PARTITIONS p4, p5
   INTO PARTITION P_new1 update indexes;
ALTER TABLE t_pe_r
   MERGE PARTITIONS p4, p5
   INTO PARTITION P_new1 ;
ALTER TABLE t_pe_l
   MERGE PARTITIONS p4, p5
   INTO PARTITION P_new1 update indexes;
ALTER TABLE t_pe_l
   MERGE PARTITIONS p4, p5
   INTO PARTITION P_new1 ;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-757019/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-757019/