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;
*
