Oracle之分区truncate、drop、add等索引失效分分析
来源:互联网 发布:产品排名优化流程 编辑:程序博客网 时间:2024/06/07 11:45
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';
- Oracle之分区truncate、drop、add等索引失效分分析
- Oracle 12C 新特性之表分区带 异步全局索引异步维护(一次add、truncate、drop、spilt、merge多个分区)
- Oracle分区表drop和truncate partition索引失效问题
- Oracle分区表drop和truncate partition索引失效问题
- 分区索引truncate某个分区,分区索引不失效
- oracle drop、truncate 分区表分区注意
- 全局索引 truncate有数据的分区,索引失效
- oracle之drop、delete及truncate区别
- Oracle12c中性能优化&功能增强新特性之全局索引DROP和TRUNCATE 分区的异步维护
- Oracle12c中性能优化&功能增强新特性之全局索引DROP和TRUNCATE 分区的异步维护
- Oracle truncate,delete,drop
- [ oracle ] oracle之路2---truncate,delete,drop
- oracle 索引失效分析步骤
- Partition Tabel测试drop和truncate 分区对全局和本地索引是否有影响
- oracle之truncate table 的drop storage和reuse storage
- oracle 中的 truncate,delete,drop
- oracle drop/truncate table 恢复
- oracle--delete&drop&truncate区别
- 贪心法-区间问题-今年暑假不AC
- 【正则表达式】正则表达式进阶
- Java注解基本原理
- Map 的 使用方法和内容
- tcp/ip学习笔记--第4章 arp协议
- Oracle之分区truncate、drop、add等索引失效分分析
- 加班的那点事儿
- Linux下vsftp的安装及配置
- Linux下快速替换文本中的字符串
- 调试远程服务项目程序代码——Eclipse调试远程服务器(亲测步骤)
- 当技术遇上管理
- VS2013 中 处理winnt.h 的 Error C2146时系统拒绝访问的解决方法
- 使用Session防止表单重复提交_JavaWeb学习
- Java SublimeText 编译环境配置及基础程序