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;
*
第 1 行出现错误:
ORA-01502: 索引 'SCOTT.INDEX_T2' 或这类索引的分区处于不可用状态
set autotrace off;
SQL> alter index index_t2 rebuild;
索引已更改。
SQL> set autotrace on exp;
SQL>
SQL> select * from t2 where sid<10;
SID SNAME
---------- ----------
1 ocpyang
2 ocpyang
3 ocpyang
4 ocpyang
5 ocpyang
6 ocpyang
7 ocpyang
8 ocpyang
9 ocpyang
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1511 | 30220 | 102 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 1511 | 30220 | 102 (0)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SID"<10)
Note
-----
- SQL plan baseline "SQL_PLAN_51mj7ynvy9stnb860bcf2" used for this statement
重建索引后恢复正常.这里只是验证了skip_unusable_indexes为false、索引设置为不可见时
对select的影响,其实对insert、update、delete的影响一样.