oracle11g 不可见索引

索引维护是DBA的一项重要工作。当一个系统运行很长一段时间，经过需求变更、结构设计变化后，系统中就可能会存在一些不会被使用的索引，或者使用效率很低的索引。这些索

引的存在，不仅占用系统空间，而且会降低事务效率，增加系统的waits。因此，我们需要找出那些无用或低效索引的索引并删除它们（找出无用索引可以通过索引监控 的方法）

。但是，直接删除索引还是存在一定风险的。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被

删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。
    11G之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引

时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。
    在11g里，Oracle 提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。我们可以在创建索引时指定invisible属性或者用

alter语句来修改索引为invisible。
在session级别设置参数optimizer_use_invisible_indexes为true，系统就可以使用invisible索引。
alter session set optimizer_use_invisible_indexes=true;

SQL代码

LIBIN@tran>select count(*) from all_objects;

  COUNT(*)
----------
     55622

LIBIN@tran>create table t_test1 as select * from all_objects;

表已创建。

LIBIN@tran>desc all_objects;
名称                                                                                                          
-----------------------------------------------------------------------------------------------------------
OWNER                                                                                                         
OBJECT_NAME                                                                                                   
SUBOBJECT_NAME                                                                                                
OBJECT_ID                                                                                                     
DATA_OBJECT_ID                                                                                                
OBJECT_TYPE                                                                                                   
CREATED                                                                                                       
LAST_DDL_TIME                                                                                                 
TIMESTAMP                                                                                                     
STATUS                                                                                                        
TEMPORARY                                                                                                     
GENERATED                                                                                                     
SECONDARY                                                                                                     
NAMESPACE                                                                                                     
EDITION_NAME                                                                                                  

LIBIN@tran>create index t_test1_idx on t_test1(object_name);

索引已创建。

LIBIN@tran>begin
  2  dbms_stats.gather_table_stats(user,'t_test1');
  3  end;
  4  /

PL/SQL 过程已成功完成。

LIBIN@tran>set autotrace trace only;
SP2-0158: 未知的 SET 选项 "only"
LIBIN@tran>set autotrace traceonly;

LIBIN@tran>select * from t_test1 where object_name like 'LIBIN%';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   196 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" LIKE 'LIBIN%')
       filter("OBJECT_NAME" LIKE 'LIBIN%')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1124  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

LIBIN@tran>


LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME                                                   USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX                                                  NO     VISIBLE

LIBIN@tran>


LIBIN@tran>alter index t_test1_idx invisible;

索引已更改。

LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME                                                   USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX                                                  NO     INVISIBLE

LIBIN@tran>

LIBIN@tran>select * from t_test1 where object_name like 'LIBIN%';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |   196 |   226   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T_TEST1 |     2 |   196 |   226   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE 'LIBIN%')


统计信息
----------------------------------------------------------
        253  recursive calls
          0  db block gets
        869  consistent gets
          4  physical reads
          0  redo size
       1124  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

LIBIN@tran>



LIBIN@tran>alter index t_test1_idx visible;

索引已更改。


LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME                                                   USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX                                                  NO     VISIBLE


LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx)  */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   196 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" LIKE 'LIBIN%')
       filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>alter index t_test1_idx invisible;

索引已更改。

LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME                                                   USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX                                                  NO     INVISIBLE

LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx)  */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |   196 |   226   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T_TEST1 |     2 |   196 |   226   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择13行。

LIBIN@tran>alter session set optimizer_use_invisible_indexes=true;

会话已更改。

LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx)  */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   196 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" LIKE 'LIBIN%')
       filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>select * from  t_test1 where object_name like 'LIBIN%';

未选定行

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   196 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" LIKE 'LIBIN%')
       filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>