11gR1中推出了“不可见索引”（Invisible Indexes）技术，这个新技术为DBA进行索引维护带来了非常大的便利。
在11gR1版本之前提供的是“不可用索引”（Unusable indexes）技术，这个技术的不便之处就是，一旦设置了索引为“不可用索引”，所有后续的DML操作将不会在索引上得到体现，也就是说，索引已经失去了存在的价值。而11g的“不可见索引”技术恰恰解决了这个问题，可以随时设置索引的可见与否，索引仍然维护DML的变化，虽然维护索引需要一定的开销，但是与其带来的便利性相比，意义是重大的。

通过一个实验，看一下“不可见索引”（Invisible Indexes）的创建和使用。

1.创建测试表T
sec@11gR2> create table t as select * from all_objects;

Table created.

sec@11gR2> desc t;
Name                Null?    Type
------------------- -------- ------------------------
OWNER               NOT NULL VARCHAR2(30)
OBJECT_NAME         NOT NULL VARCHAR2(30)
SUBOBJECT_NAME               VARCHAR2(30)
OBJECT_ID           NOT NULL NUMBER
DATA_OBJECT_ID               NUMBER
OBJECT_TYPE                  VARCHAR2(19)
CREATED             NOT NULL DATE
LAST_DDL_TIME       NOT NULL DATE
TIMESTAMP                    VARCHAR2(19)
STATUS                       VARCHAR2(7)
TEMPORARY                    VARCHAR2(1)
GENERATED                    VARCHAR2(1)
SECONDARY                    VARCHAR2(1)
NAMESPACE           NOT NULL NUMBER
EDITION_NAME                 VARCHAR2(30)

2.无索引情况下执行计划
sec@11gR2> set autot trace exp
sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    17 | 2686 |   285   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T    |    17 | 2686 |   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

3.创建“不可见索引”（Invisible Indexes）
sec@11gR2> create index i_t on t(object_id) invisible;

Index created.

4.在具有不可见索引的表T上执行查询操作
sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    17 | 2686 |   285   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T    |    17 | 2686 |   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

可见，此时创建的索引没有被SQL语句采纳。索引被忽略。

5.修改“不可见索引”为正常索引，再次查看执行计划
sec@11gR2> alter index i_t visible;

Index altered.

sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

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

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

   2 - access("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

可见，此时我们创建的索引I_T已经被使用。

再次将索引修改为“不可见索引”的方法如下：
sec@11gR2> alter index i_t invisible;

Index altered.

sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    17 | 2686 |   285   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T    |    17 | 2686 |   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

6.使用optimizer_use_invisible_indexes参数控制“不可见索引”是否生效
当参数optimizer_use_invisible_indexes为“FALSE”（默认值）时，表示“不可见索引”类型的索引不被优化器使用；
当参数optimizer_use_invisible_indexes为“TRUE”（默认值）时，表示“不可见索引”类型的索引与正常所以无异；

关于optimizer_use_invisible_indexes参数介绍信息请参考Oracle官方文档中的描述：http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams167.htm#REFRN10301
1）在session级别修改optimizer_use_invisible_indexes为“TRUE”
sec@11gR2> alter session set optimizer_use_invisible_indexes=true;

Session altered.

sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

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

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

   2 - access("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

查询已经使用索引。

2）在session级别修改optimizer_use_invisible_indexes为“FALSE”
sec@11gR2> alter session set optimizer_use_invisible_indexes=false;

Session altered.

sec@11gR2> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    17 | 2686 |   285   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T    |    17 | 2686 |   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=666)

Note
-----
   - dynamic sampling used for this statement (level=2)

索引不被优化器考虑，此时重新选择全表扫描的方式获取数据。

7.小结
“不可见”的概念就是，Oracle还是在不停地维护这个索引，只是在SQL执行过程中不再考虑该索引——视索引为无物。
有了这个技术，当我们可以在不同时间段启用不同的索引，以便比秒索引被反复删除和添加。

Good luck.

secooler
10.05.07

-- The End --

【INDEX】11g中利用不可见索引降低索引维护时对系统的冲击