当前位置:Gxlcms > mysql > Oracle中addsplitpartition对globallocalindex的影响

Oracle中addsplitpartition对globallocalindex的影响

时间:2021-07-01 10:21:17 帮助过:12人阅读

生产库中某些大表的分区异常,需要对现有表进行在线操作,以添加丢失分区,因为是生产库,还是谨慎点好,今天有空,针对addspli

生产库中某些大表的分区异常,需要对现有表进行在线操作,以添加丢失分区,因为是生产库,还是谨慎点好,今天有空,针对add&split分区对global&local索引的影响进行了测试,测试版本为Oracle11.2.0.4,过程如下:

首先,创建分区表:

CREATE TABLE TP1
(
C1 INT PRIMARY KEY,
C2 VARCHAR2(10),
C3 CHAR(10)
)
partition by range (c1)
(
partition p1 values less than(6),
partition p2 values less than(11),
partition p3 values less than(16),
partition maxvalue values less than(maxvalue));
但在add partition时遇到了问题,报错如下:

ORA-14074: 分区界限必须调整为高于最后一个分区界限


根本原因是存在最后maxvalue分区,于是,再创建一个不带maxvalue的分区表:

CREATE TABLE TP2
(
C1 INT PRIMARY KEY,
C2 VARCHAR2(10),
C3 CHAR(10)
)
partition by range (c1)
(
partition p1 values less than(6),
partition p2 values less than(11),
partition p3 values less than(16));


然后,,创建local索引:

create index idx_tp2_c2 on tp2(c2) local;

因为主键就是个global索引,所以,不需要另外创建global index,我们insert数据:

insert into tp2 vlaues(1,'aaa','aaa');

insert into tp2 vlaues(2,'aaa','aaa');


insert into tp2 vlaues(3,'aaa','aaa');

insert into tp2 vlaues(4,'aaa','aaa');


insert into tp2 vlaues(5,'aaa','aaa');
insert into tp2 vlaues(6,'aaa','aaa');

insert into tp2 vlaues(7,'aaa','aaa');

insert into tp2 vlaues(8,'aaa','aaa');


insert into tp2 vlaues(9,'aaa','aaa');

insert into tp2 vlaues(10,'aaa','aaa');

commit;

我们add partition:

ALTER TABLE tp2 add PARTITION p10 values less than(51);

add partition成功,因为不存在maxvalue分区,而且,add partition对global和local索引均无影响;


那么,split partition:

ALTER TABLE tp2 SPLIT PARTITION p2 AT (8) INTO (PARTITION p2, PARTITION p22) ;


发现split分区会导致golbal索引失效,而local索引正常,为split分区命令添加update indexes选项后,再split分区对global&local索引均无影响:

ALTER TABLE tp2 SPLIT PARTITION p1 AT (3) INTO (PARTITION p1, PARTITION p11) update indexes;

以上为测试结果,记录于此,以便今后其他同学和自己参考。

本文永久更新链接地址

人气教程排行