【www.bbyears.com--Oracle教程】
SKIP_UNUSABLE_INDEXES的使用与索引失效是相关的,该参数10g开始引入,11g默认为TRUE.
当为TRUE时候,如果数据库中存在usable状态的索引,则会自动忽略该索引生成新的执行计划(不走该索引,也不提示该索引的异常);当为False时候,则会报错.我所运维的数据库在一些关键系统中,会将此参数设成False,让系统及时发现索引的异常以便及时去介入修复.
环境各有所异,设置值也可依据实际情况设置.如果sql使用了hint或者涉及到唯一索引的对应DML,该参数会失效.
该参数的一些使用场景可以参考如下的测试:
创建测试表和索引
SQL> conn test/test
已连接。
SQL> drop table a;
表已删除。
SQL> create table a(id number);
表已创建。
SQL> create unique index idx_a_id on a(id);
索引已创建。
SQL> declare
2 begin
3 for a in 1..1000 loop
4 insert into a(id) values(a);
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME TYPE VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes boolean TRUE
SQL> select * from a where id=1;
执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
124 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
修改skip_unusable_indexes为false
SQL> alter system set skip_unusable_indexes=false scope=memory;
系统已更改。
将索引修改为不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出现错误提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 "TEST.IDX_A_ID" 或这类索引的分区处于不可用状态
将skip_unusable_indexes修改为true
SQL> alter system set skip_unusable_indexes=true scope=memory;
系统已更改。
对于查询操作此时该sql能够正常运行，但是此时进行的是全表扫描
SQL> select * from a where id=1;
执行计划
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用hint强制使用索引，此时会提示索引无效
SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 "TEST.IDX_A_ID" 或这类索引的分区处于不可用状态
--插入操作会出错
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出现错误:
ORA-01502: 索引 "TEST.IDX_A_ID" 或这类索引的分区处于不可用状态
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 "TEST.IDX_A_ID" 或这类索引的分区处于不可用状态
SQL>
解决方法，重建索引
SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;
执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
统计信息
----------------------------------------------------------
15 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index test.idx_a_id;
索引已删除。
SQL> create index test.idx_a_id on a(id);
索引已创建。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已创建 1 行。
SQL> commit;
测试证明SKIP_UNUSABLE_INDEXES对于使用hint强制使用索引的语句和唯一索引的插入、删除语句却不能生效。
该测试摘自互联网,同时做了一些修改.