分区操作后分区索引和全局索引的状态

最新推荐文章于 2023-03-25 08:54:18 发布

Mr.Lpp

最新推荐文章于 2023-03-25 08:54:18 发布

阅读量899

点赞数

分类专栏： oracle管理

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/u012232730/article/details/104533245

版权

oracle管理专栏收录该内容

102 篇文章 1 订阅

订阅专栏

一、对空分区split和merge操作后的状态

1、创建分区表
create table test_part_index
(
id int,
name varchar2(20)
)
partition by range (id)
(partition p1 values less than (5),
partition p2 values less than (10),
partition p3 values less than (15),
partition p4 values less than (maxvalue)
);
2、创建全局和分区索引
create index ind_g_id on test_part_index(id);
create index ind_l_name on test_part_index(name) local;
3、插入测试数据
insert into test_part_index values (3,'lpp');
insert into test_part_index values (6,'lpp01');
commit;
4、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
--索引状态正常
5、对空的maxvalue分区进行拆分

alter table test_part_index split partition p4 at (20) into (partition p4,partition p5);

6、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
7、合并p4,p5两个空分区
alter table test_part_index merge partitions p4,p5 into partition p5;--注意只能用名称P5，否则会报错

8、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常

二、对存在数据的分区进行split和merge后的操作导致的状态

9、对maxvalue分区插入数据
insert into test_part_index values (23,'lpp02');
commit;
10、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   USABLE
11、对maxvalue分区进行拆分
alter table test_part_index split partition p5 at (20) into (partition p4,partition p5);
12、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
13、合并P4,P5两分区
alter table test_part_index merge partitions p4,p5 into partition p5;
14、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID UNUSABLE

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   UNUSABLE
--全局索引失效，P5分区索引失效
15、重建失效索引
alter index IND_G_ID rebuild;
alter index IND_L_NAME rebuild partition p5;
16、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   USABLE
17、再对maxvalue分区插入一条数据
insert into test_part_index values (18,'lpp03');
commit;
18、查看数据及rowid
id name rowid
3   lpp   AAAV1hAAEAAADywAAA
6   lpp01   AAAV1iAAEAAAECuAAA
23   lpp02   AAAV11AAEAAAFCSAAA
18   lpp03   AAAV11AAEAAAFCxAAA

19、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
20、对maxvalue分区进行拆分
alter table test_part_index split partition p5 at (20) into (partition p4,partition p5);
21、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID UNUSABLE

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   UNUSABLE
IND_L_NAME   P5   UNUSABLE
--全局索引失效，P4,P5分区索引失效
22、查看数据及rowid
id name rowid
3   lpp   AAAV1hAAEAAADywAAA
6   lpp01   AAAV1iAAEAAAECuAAA
18   lpp03   AAAV19AAEAAAEySAAA
23   lpp02   AAAV1+AAEAAAEiSAAA
--rowid发生变化
23、重建失效索引
alter index IND_G_ID rebuild;
alter index IND_L_NAME rebuild partition p4;
alter index IND_L_NAME rebuild partition p5;
24、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
25、合并P4,P5两分区
alter table test_part_index merge partitions p4,p5 into partition p5;
26、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID UNUSABLE

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   UNUSABLE
--全局索引失效，P5分区索引失效
27、查看数据及rowid
id name rowid
3   lpp   AAAV1hAAEAAADywAAA
6   lpp01   AAAV1iAAEAAAECuAAA
18   lpp03   AAAV2EAAEAAAFCSAAA
23   lpp02   AAAV2EAAEAAAFCSAAB
--rowid发生变化
28、重建失效索引
alter index IND_G_ID rebuild;
alter index IND_L_NAME rebuild partition p5;
29、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P5   USABLE
30、删除id为23的数据
delete from TEST_PART_INDEX where id=23;
commit;
31、对maxvalue分区进行拆分
alter table test_part_index split partition p5 at (20) into (partition p4,partition p5);
32、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
33、查看数据及rowid
id name rowid
3   lpp   AAAV1hAAEAAADywAAA
6   lpp01   AAAV1iAAEAAAECuAAA
18   lpp03   AAAV19AAEAAAEySAAA
--rowid未发生变化

三、对空分区truncate和drop后的索引状态

34、truncate空的maxvalue分区
alter table TEST_PART_INDEX truncate partition p5;
35、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
36、drop空的maxvalue分区
alter table TEST_PART_INDEX drop partition p5;
37、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
--索引状态正常，p5分区索引消失
38、新增maxvalue分区
alter table TEST_PART_INDEX add partition p5 values less than (maxvalue);
39、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常

四、对存在数据的分区进行truncate和drop后的索引状态

40、插入数据到maxvalue分区
insert into test_part_index values (24,'lpp02');
commit;
41、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
42、truncate maxvalue分区
alter table TEST_PART_INDEX truncate partition p5;
43、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID UNUSABLE

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--全局索引失效
44、重建全局索引
alter index IND_G_ID rebuild;
45、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID VALID

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
IND_L_NAME   P5   USABLE
--索引状态正常
46、插入数据到maxvalue分区
insert into test_part_index values (24,'lpp02');
commit;
46、drop maxvalue分区
alter table TEST_PART_INDEX drop partition p5;
47、查看索引状态
select index_name,status from user_indexes where table_name='TEST_PART_INDEX';
index_name status

IND_L_NAME N/A
IND_G_ID UNUSABLE

select index_name,status from user_ind_partitions where index_name='IND_L_NAME';
index_name partition_name status

IND_L_NAME   P1   USABLE
IND_L_NAME   P2   USABLE
IND_L_NAME   P3   USABLE
IND_L_NAME   P4   USABLE
--全局索引失效

五、总结：

1、针对空的分区进行split,merge,truncate和drop操作都不会导致全局和分区索引失效
2、针对有数据的分区，如果split后的数据都在某一个分区里，不会导致全局和分区索引失效，如果split后的分区都有数据，则全局索引和对应分区的分区索引都会失效
3、针对有数据的分区，merge操作会导致全局和分区索引失效
4、针对有数据的分区，truncate和drop操作都会导致全局索引失效

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
分区操作后分区索引和全局索引的状态

一、对空分区操作后的状态1、创建分区表create table test_part_index(id int,name varchar2(20))partition by range (id)(partition p1 values less than (5),partition p2 values less than (10),partition p3 values les...
复制链接

扫一扫

专栏目录

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。