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

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