融合（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 ;