skip_unusable_indexes参数对不可见索引的影响

来源：互联网发布：mac os 网络恢复编辑：程序博客网时间：2024/06/06 02:23

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的影响一样.