MSN olivenan@hotmail.com

SKIP_UNUSABLE_INDEXES参数的主要用途是当索引为usable状态时保证sql的运行，虽然它保证了系统的健壮性，但是有可能系统没有使用该索引而导致性能的低下。还有该参数对于使用hint的某些sql和唯一索引的插入、删除语句却不生效 比如使用了index。

创建测试表和索引
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强制使用索引的语句和唯一索引的插入、删除语句却不能保证。