我们来先体验一下不可见索引，然后再总结一下。
测试环境基于11gR2
> sqlplus -v
SQL*Plus: Release 11.2.0.2.0 Production
我们先来创建一个表来看看，然后创建一个不可见索引，来看看索引的访问情况。
create table t as select *from dba_objects where object_id is not null and rownum 我们随机抽取5条数据，后续的查询都会基于里面的1条数据来做说明。
select *from (select object_id from t order by dbms_random.value()) where rownum OBJECT_ID
----------
9445
4672
5048
4096
打开debug，来看看sql语句的执行情况。
set autot trace exp stat
我们选取了object_id为9445的记录。在没有索引的情况下，看看执行情况，好做比对。
select *from t where object_id=9445 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
177 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
上面的语句毫无疑问走了全表扫描，因为我们还没有创建索引。
我们来创建一个唯一性索引，来在查询中启用一下索引，这个时候索引还是可见的。
create unique index inx_t on t(object_id) ;
SQL> select *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
1 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这个时候一切都没有什么特别之处。
我们来尝试下面的步骤。把索引改为不可见索引，或者我们删除已经存在的索引，然后重建一个不可见索引。
--删除,重建索引
drop index inx_t;
create unique index inx_t on t(object_id) invisible;
--修改索引属性，改为不可见
alter index inx_t invisible;
select *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
139 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这个时候索引没有启用，感觉就跟删除了一样。其实在dml操作中还是会有消耗和正常的索引是一样的，只是在优化器中对这个索引不可见。
我们来使用hint看看是否能够正常启用。
select /*+index(t inx_t)*/ *from t where object_id=9445;
SQL> select /*+index(t inx_t)*/ *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结果尽管使用了hint，但是还是不买账，依旧没有反应。
我们来看看怎么使用不可见索引。
默认在数据库参数中，有一个参数optimizer_use_invisible_indexes，默认是不会启用不可见索引的。我们可以从session级别,system级别进行设定。一般都会在session级启用，来查看索引是否能够达到预期目标，然后再决定是否设定为可见。
SQL> show parameter vis
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
我们在session级启用。
alter session set "optimizer_use_invisible_indexes"=true;
然后执行同样的语句。可以看到索引能够正常启用了。
select *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们恢复原值。来看看，又开始走了全表扫描。
alter session set "optimizer_use_invisible_indexes"=false;
select *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果在session级别确认了索引能够极大的提高效率，可以修改索引的属性，把索引置为可见状态。
我们设置索引为可见，看看语句的执行情况，索引又能够正常启用了。
alter index inx_t visible;
select *from t where object_id=9445;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
大体的测试就这么多，还有几个问题需要考虑一下，如果不可见索引的使用这么灵活。我们可以根据不可见索引来评估一条sql语句的执行效率情况，那么是否在高可用场景中使用呢。
我们可以做一个简单的测试来说明，在创建不可见索引的时候，是否还存在着锁，这样就能够判断是否适用于高可用场景了。
我们删除表，然后创建新的表，表中的数据尽量多一些，让创建索引的时间稍微长一点。
drop table t;
create table t as select *from dba_objects where rownum create index inx_t on t(object_id) invisible;
然后我们查看锁的情况。可以看到在创建不可见索引的过程中还是存在着锁。在线业务中还是会有影响的。
SID_SERIAL ORACLE_USE OBJECT_NAME LOGON_TIM SEC_WAIT OSUSER MACHINE PROGRAM STATE STATUS LOCK_ MODE_HELD
------------ ---------- --------------- --------- -------- ---------- ---------- -------------------- ---------- ---------- ----- ----------
763,33293 N1 OBJ$ 04-DEC-14 0 testuser testdb sqlplus@xx (TNS V1-V3) WAITED SHORT TIME ACTIVE DML Row-X (SX)
763,33293 N1 T 04-DEC-14 0 testuser testdb sqlplus@xx (TNS V1-V3) WAITED SHORT TIME ACTIVE DML Share
763,33293 N1 T 04-DEC-14 0 testuser testdb sqlplus@xx (TNS V1-V3) WAITED SHORT TIME ACTIVE DL Row-X (SX)
763,33293 N1 T 04-DEC-14 0 testuser testdb sqlplus@xx (TNS V1-V3) WAITED SHORT TIME ACTIVE DL Row-X (SX)
不可见索引在数据字典视图中和普通索引没有太大的区别，都有对应的索引段，这是在索引对应的数据字典中有一个字段visibility标明索引是否可见。
select segment_name,segment_type,blocks from user_segments where segment_name='INX_T'
SEGMENT_NAME SEGMENT_TYPE BLOCKS
-------------------- ------------------ ----------
INX_T INDEX 10240
SQL> SELECT INDEX_NAME,VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME='INX_T';
INDEX_NAME VISIBILIT
------------------------------ ---------
INX_T INVISIBLE