合 Oracle之不可见索引（invisible indexes）
不可见索引(Invisible Indexes)
您常常感到疑惑，索引是否真的有利于用户的查询？它可能有利于一个查询，但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响，也会执行潜在的删除和更新操作，这取决于WHERE 条件是否在索引中包括该列。一个相关的问题是，使用索引时，如果该索引被删除，会对查询性能造成什么影响？当然，您可以删除索引并查看对查询的影响，但说起来容易做起来难。索引实际上如何有助于查询？您必须重新定义索引，为此，需要进行重新创建。完全重新创建之后，就没有人能使用它了。重新创建索引也是一个昂贵的过程；它会占用许多有用的数据库资源。
我们经常在数据库上建索引或删除索引，由于索引对SQL的执行性能影响非常大，有可能变得很好，也有可能变得很差，在线下开发环境我们可以充分测试，对于创建或删除索引没什么问题。但是在线上环境，由于高并发的访问，如果我们删除了一个重要的大索引(GB以上)，删除后才发现大量SQL性能变差，很快主机就LOAD飙升，系统无法运行了，由于索引已经删除，并且很大，要当场重建基本不可能，因为这个索引巨大，创建估计要几分钟甚至几个小时，况且这时主机已经基本没有响应，IO全部用光，只能把应用停了，等索引建好后再开始打开应用，等发生这样的事才会为自己的失误而后悔。那我们有没有办法让删除索引的风险降低呢，答案是有！即本文介绍的不可见索引。
索引维护是DBA的一项重要工作。当一个系统运行很长一段时间，经过需求变更、结构设计变化后，系统中就可能会存在一些不会被使用的索引，或者使用效率很低的索引。这些索引的存在，不仅占用系统空间，而且会降低事务效率，增加系统的waits。因此，我们需要找出那些无用或低效索引的索引并删除它们（找出无用索引可以通过索引监控的方法）。但是，直接删除索引还是存在一定风险的。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。11G之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。在11g里，Oracle 提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。
从版本11g 开始，可以创建不可见的索引。优化程序会忽略不可见的索引，除非在会话或系统级别上将 OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE。此参数的默认值是FALSE。
使索引不可见是使索引不可用或删除索引的一种替代办法。使用不可见的索引，可完成以下操作：
(1) 在删除索引之前测试对索引的删除。
(2) 对应用程序的特定操作或模块使用临时索引结构，这样就不会影响整个应用程序。
注意：与不可用的索引不同，不可见的索引在使用DML 语句期间仍会得到维护。
当索引不可见时，优化程序生成的计划不会使用该索引。如果未发现性能下降，则可以删除该索引。还可以创建最初不可见的索引，执行测试，然后确定是否使该索引可见。
可以查询*_INDEXES 数据字典视图的VISIBILITY 列来确定该索引是VISIBLE 还是INVISIBLE。
SQL> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_ID';
VISIBILIT
---------
VISIBLE
--创建不可见索引：
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE;
--修改索引是否可见：
ALTER INDEX INDEX_NAME INVISIBLE;
ALTER INDEX INDEX_NAME VISIBLE;
特点总结：
1、当索引变更为不可见的时候，只是对oracle的优化器不可见。
2、不可见索引在DML操作的时候也会被维护。
3、加HNIT对不可见索引无效。
4、可以通过修改system级别和session级别参数来使用不可见索引。
我的示例
创建表，不可见索引，并收集统计信息：
SYS@lhrdb> CREATE TABLE T_II_20160819_01_LHR AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@lhrdb> CREATE INDEX IDX_II_20160819 ON T_II_20160819_01_LHR(OBJECT_ID) INVISIBLE;
Index created.
SYS@lhrdb> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_II_20160819';
VISIBILIT
---------
INVISIBLE
SYS@lhrdb> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>'T_II_20160819_01_LHR',DEGREE=>2,CASCADE => TRUE);
PL/SQL procedure successfully completed.
--带where条件查询：
SYS@lhrdb> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SYS@lhrdb> set line 9999
SYS@lhrdb> set autot traceonly exp
SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 700947541
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 343 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_II_20160819_01_LHR | 1 | 98 | 343 (2)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
--这里使用了全表扫描，根据唯一性，这里应该走索引的，我们加上hint试试
SYS@lhrdb> SELECT /*+index(T,IDX_II_20160819)*/ * FROM T_II_20160819_01_LHR T WHERE OBJECT_ID=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 700947541
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 343 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_II_20160819_01_LHR | 1 | 98 | 343 (2)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
--对于invisible的index，使用hint也没有用。
--修改OPTIMIZER_USE_INVISIBLE_INDEXES参数为TRUE，再次查询：
SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.
SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2544197461
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR | 1 | 98 | 2 (0)| 00:00:01 |
