skip_unusable_indexes参数对不可见索引的影响
数据库如何处理不可用索引主要 由参数skip_unusable_indexes决定的；
如果该参数设置为true则数据库遇到不可用索引 时，只会忽略而不会提示任何错误信息；同时即使该表上带有不可用的索引或索引分区，也 可以针对该表执行DML操作，针对不可用索引对应的DML语句都将正常执行，但是数据库停止 维护相关索引.
show parameters skip_unusable_indexes;
-----数据库忽略 索引执行全表扫描
create table t2
(
sid int not null ,
sname varchar2 (10)
)
tablespace test;
--循环导入数据
declare
maxrecords constant int:=100000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t2 values (i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
create index index_t2 on t2(sid) tablespace pindex;
set autotrace on exp;
select * from t2 where sid<10;
执行计划
---------------------------------------------------- ------
Plan hash value: 48609158
--------------------------------------- -----------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
------- -------------------------------------------------------------------------
---- ----
| 0 | SELECT STATEMENT | | 9 | 180 | 3 (0)| 00
:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 9 | 180 | 3 (0)| 00
:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_T2 | 9 | | 2 (0)| 00
:00:01 |
-------------------------------- ------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------ ---
2 - access("SID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
set autotrace off;
alter index index_t2 unusable;
set autotrace on exp;
select * from t2 where sid<10;
执行计划
------------------ ----------------------------------------
Plan hash value: 1513984157
--- -----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------- -------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 4 | 80 | 103 (1)| 00:00:02 |
--------------------------------- -----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_51mj7ynvy9stnb860bcf2" used for this statement
set autotrace off;
----- 验证skip_unusable_indexes为false的影响
show parameters skip_unusable_indexes;
alter system set skip_unusable_indexes=false;
alter index index_t2 unusable;
set autotrace on exp;
select * from t2 where sid<10;
*