合并分区表分区

使用 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;

实验操作脚本

1. --清理环境
   
2. drop table vast.hepart;
   
3. --创建一个分区表
   
4. CREATE TABLE vast.hepart
   
5.     ( prod_id NUMBER(6) primary key,
   
6.       xname varchar2(20),
   
7.       lname varchar2(20),
   
8.       time_id DATE)
   
9.   PARTITION BY RANGE (time_id)
   
10.     ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
    
11.       PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
    
12.       PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
    
13.       PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')));
    
14. --创建索引
    
15. --在建表是已经有一个唯一的主键索引，并且是global的
    
16. --创建一个一般索引
    
17. create index vast.idn_hepart_xname on vast.hepart(xname);
    
18. --创建一个本地索引（分区索引）
    
19. create index vast.idn_hepart_lname on vast.hepart(lname) local;
    
20. --插入测试数据，一个分区一条
    
21. insert into vast.hepart values (1,'a','a',to_date('2007-05-05','yyyy-mm-dd'));
    
22. insert into vast.hepart values (2,'b','b',to_date('2008-05-06','yyyy-mm-dd'));
    
23. insert into vast.hepart values (3,'c','c',to_date('2009-02-05','yyyy-mm-dd'));
    
24. insert into vast.hepart values (4,'d','d',to_date('2009-12-15','yyyy-mm-dd'));
    
25. commit;
    
26. --按分区查数据
    
27. select * from vast.hepart partition(p0);
    
28. select * from vast.hepart partition(p1);
    
29. select * from vast.hepart partition(p2);
    
30. select * from vast.hepart partition(p3);
    
31. --查看此时索引状态
    
32. select i.status,i.* from dba_indexes i where i.table_name='HEPART';
    
33. select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
    
34. select pi.* from dba_part_indexes pi where pi.table_name='HEPART';
    
35. --新增三个分区
    
36. alter table vast.hepart
    
37. add partition p4 values less than (TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
    
38. alter table vast.hepart
    
39. add partition p5 values less than (TO_DATE(' 2030-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
    
40. alter table vast.hepart
    
41. add partition p6 values less than (TO_DATE(' 2050-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
    
42. select * from dba_tab_partitions p where p.table_name='HEPART';
    
43. 
    
44. --像新分区插入数据
    
45. insert into vast.hepart values (5,'e','e',to_date('2010-05-05','yyyy-mm-dd'));
    
46. insert into vast.hepart values (6,'f','f',to_date('2022-05-06','yyyy-mm-dd'));
    
47. insert into vast.hepart values (7,'g','g',to_date('2038-02-05','yyyy-mm-dd'));
    
48. commit;
    
49. --查看新分区的记录
    
50. select * from vast.hepart partition(p4);
    
51. select * from vast.hepart partition(p5);
    
52. select * from vast.hepart partition(p6);
    
53. 
    
54. --开始合并p4 p5分区
    
55. ALTER TABLE vast.hepart MERGE PARTITIONS p4,p5 INTO PARTITION p5 ;
    
56. select * from dba_tab_partitions p where p.table_name='HEPART';
    
57. select * from vast.hepart partition(p5);
    
58. select i.status,i.* from dba_indexes i where i.table_name='HEPART';
    
59. select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
    
60. select pi.* from dba_part_indexes pi where pi.table_name='HEPART';
    
61. --此时索引失效，本地索引对应分区索引失效，故要加上update indexes
    
62. ALTER TABLE vast.hepart MERGE PARTITIONS p5,p6 INTO PARTITION p6 update indexes;
    
63. select * from dba_tab_partitions p where p.table_name='HEPART';
    
64. select * from vast.hepart partition(p6);
    
65. select i.status,i.* from dba_indexes i where i.table_name='HEPART';
    
66. select ip.status,ip.* from dba_ind_partitions ip where ip.index_name in ('IDN_HEPART_XNAME','IDN_HEPART_LNAME');
    
67. 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/