Oracle之分区truncate、drop、add等索引失效分分析

最新推荐文章于 2022-10-29 15:43:52 发布

一个阳光努力的程序盐

最新推荐文章于 2022-10-29 15:43:52 发布

阅读量2.6k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle之分区表和分区表优化

本文链接：https://blog.youkuaiyun.com/qq_33879355/article/details/78307654

Oracle之分区表和分区表优化专栏收录该内容

9 篇文章

订阅专栏

本文探讨了在Oracle数据库中，不同分区操作如TRUNCATE、DROP、SPLIT、ADD和EXCHANGE等对全局和局部索引的影响。通过具体实验案例分析了各种情况下索引的有效性变化，并给出了相应的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

truncate分析：

结论：全局索引truncate 分区和交换分区都会导致索引失效果
局部索引truncate分区不会导致索引失效。

此处只说索引失效的场景（只会影响全局索引）：
结论：全局索引truncate 分区和交换分区都会导致索引失效果
局部索引truncate分区不会导致索引失效。

connect ljb/ljb
drop table part_tab_trunc purge;
create table part_tab_trunc (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
;
insert into part_tab_trunc select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=50000;
commit;
create index idx_part_trunc_col2 on part_tab_trunc(col2) local;
create index idx_part_trunc_col3 on part_tab_trunc(col3) ;

---分区truncate前
select index_name, partition_name, status
from user_ind_partitions
where index_name = 'IDX_PART_TRUNC_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_TRUNC_COL2 P1 USABLE
IDX_PART_TRUNC_COL2 P2 USABLE
IDX_PART_TRUNC_COL2 P3 USABLE

select index_name, status
from user_indexes
where index_name = 'IDX_PART_TRUNC_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_TRUNC_COL3 VALID

alter table part_tab_trunc truncate partition p1 ;

---分区truncate后
select index_name, partition_name, status
from user_ind_partitions
where index_name = 'IDX_PART_TRUNC_COL2';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_TRUNC_COL2 P1 USABLE
IDX_PART_TRUNC_COL2 P2 USABLE
IDX_PART_TRUNC_COL2 P3 USABLE

select index_name, status
from user_indexes
where index_name = 'IDX_PART_TRUNC_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_TRUNC_COL3 UNUSABLE

drop分析：

结论：如果加上Update GLOBAL indexes全局索引不会失效，否则全局索引失效

--此处只说索引失效的场景（也是只影响全局索引）：

--试验1（未加Update GLOBAL indexes关键字）

drop table part_tab_drop purge;
create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
;
insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;
commit;

create index idx_part_drop_col2 on part_tab_drop(col2) local;
create index idx_part_drop_col3 on part_tab_drop(col3) ;

--未drop分区之前
select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_DROP_COL3 VALID

alter table part_tab_drop drop partition p1 ;

--已drop分区之后
select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_DROP_COL3 UNUSABLE

--试验2（加Update GLOBAL indexes关键字）
drop table part_tab_drop purge;
create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
;
insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;
commit;

create index idx_part_drop_col2 on part_tab_drop(col2) local;
create index idx_part_drop_col3 on part_tab_drop(col3) ;

--未drop分区之前
select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_DROP_COL3 VALID

alter table part_tab_drop drop partition p1 Update GLOBAL indexes;

--已drop分区之后
select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_DROP_COL3 VALID

spilt分析：

--结论是:split会导致全局索引失效，也会导致局部索引失效。(假如P7分区没数据则局部索引不会失效）

--重建索引
--local索引重建
select b.table_name,
a.INDEX_NAME,
a.PARTITION_NAME,
a.STATUS,
'alter index ' || a.index_name || ' rebuild partition ' ||partition_name || ';' 重建列
from USER_IND_PARTITIONS a, user_part_indexes b
where a.index_name = b.index_name
and b.TABLE_NAME IN ('PART_TAB_SPLIT')
and STATUS = 'UNUSABLE'
ORDER BY b.table_name, a.INDEX_NAME, a.PARTITION_NAME;

--全局索引重建
alter index idx_part_split_col3 rebuild;

add分析：

---结论：看来add不会导致全局和局部索引失效！

exchange分析：

结论：如果加上加Update GLOBAL indexes关键字，全局索引就不会失效

/*
此处只说索引失效的场景（只会影响全局索引）：
结论：全局索引truncate 分区和交换分区都会导致索引失效果
局部索引truncate分区不会导致索引失效。但是如果交换分区的时候，交换的临时表没有索引，或者
有索引，没有用including indexes的关键字，会导致局部的索引失效，就是某个分区失效
重建局部索引只能用alter index local_idx rebuild partition p1这样的方式
*/

--分区表的exchange
connect ljb/ljb
drop table part_tab_exch purge;
create table part_tab_exch (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_exch select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=60000;
commit;

create index idx_part_exch_col2 on part_tab_exch(col2) local;
create index idx_part_exch_col3 on part_tab_exch (col3);

--分区表的EXCHANGE（某分区和普通表之间的数据进行交换）
drop table normal_tab purge;
create table normal_tab (id int,col2 int,col3 int,contents varchar2(4000));
create index idx_norm_col2 on normal_tab (col2);

--未exchange 分区之前
select index_name,partition_name,status from user_ind_partitions where index_name='IDX_PART_EXCH_COL2';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_EXCH_COL2 P1 USABLE
IDX_PART_EXCH_COL2 P2 USABLE
IDX_PART_EXCH_COL2 P3 USABLE
IDX_PART_EXCH_COL2 P4 USABLE
IDX_PART_EXCH_COL2 P5 USABLE
IDX_PART_EXCH_COL2 P_MAX USABLE

select index_name,status from user_indexes where index_name='IDX_PART_EXCH_COL3';
INDEX_NAME STATUS
------------------------------ --------
IDX_PART_EXCH_COL3 VALID

alter table part_tab_exch exchange partition p1 with table normal_tab including indexes;

--exchange分区后
select index_name,partition_name,status from user_ind_partitions where index_name='IDX_PART_EXCH_COL2';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_PART_EXCH_COL2 P1 USABLE
IDX_PART_EXCH_COL2 P2 USABLE
IDX_PART_EXCH_COL2 P3 USABLE
IDX_PART_EXCH_COL2 P4 USABLE
IDX_PART_EXCH_COL2 P5 USABLE
IDX_PART_EXCH_COL2 P_MAX USABLE

select index_name,status from user_indexes where index_name='IDX_PART_EXCH_COL3';

INDEX_NAME STATUS
------------------------------ --------
IDX_PART_EXCH_COL3 UNUSABLE

---注意，如果加上update global indexes 关键字，这个IDX_PART_EXCH_COL3就不会失效，请自行试验。

--试验2（加Update GLOBAL indexes关键字）

--分区表的exchange
connect ljb/ljb
drop table part_tab_exch purge;
create table part_tab_exch (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_exch select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=60000;
commit;

create index idx_part_exch_col2 on part_tab_exch(col2) local;
create index idx_part_exch_col3 on part_tab_exch (col3);

--分区表的EXCHANGE（某分区和普通表之间的数据进行交换）
drop table normal_tab purge;
create table normal_tab (id int,col2 int,col3 int,contents varchar2(4000));
create index idx_norm_col2 on normal_tab (col2);

--未exchange 分区之前
select index_name,partition_name,status from user_ind_partitions where index_name='IDX_PART_EXCH_COL2';
select index_name,status from user_indexes where index_name='IDX_PART_EXCH_COL3';

alter table part_tab_exch exchange partition p1 with table normal_tab including indexes Update GLOBAL indexes;

--exchange分区后
select index_name,partition_name,status from user_ind_partitions where index_name='IDX_PART_EXCH_COL2';
select index_name,status from user_indexes where index_name='IDX_PART_EXCH_COL3';