不可见索引简介
从Oracle 11g开始，可以创建不可见索引（invisible index）。默认情况下，优化器会忽略invisible index，不使用invisible index，即使添加了相关索引hint，也不会使用invisible index。
初始化参数optimizer_use_invisible_indexes决定优化器是否使用invisible index，其默认值为false，即默认不使用invisible index。但如果在session级别或者system级别上将optimizer_use_invisible_indexes初始化参数设置为true，那么就可以使用invisible index。
与不可用索引(unusable index)不同，invisible index在使用DML语句期间仍会得到维护。
Oracle引入不可见索引是有用途的，使索引不可见是使索引不可用或者删除索引的一种替代办法。
在删除索引之前,将索引修改为不可见，观察是否会产生影响，以便判断索引是否可以删除。
当索引不可见时，优化器生成的执行计划不会使用该索引。删除索引时，可以先将索引修改为invisible，如果未发生性能下降问题，则可以删除该索引。在表上新建索引时，可以先创建一个最初不可见的索引，然后执行测试，看索引的效率怎么样，最后确定是否使该索引可见，是否使用该索引。
可以查看dba_indexes、all_indexes、user_indexes视图的visibility字段来确定该索引是可见索引还是不可见索引，visible表示可见，invisible表示不可见。
不可见索引测试
下面做一些简单的测试。
创建不可见索引
先创建tab表，然后在表上创建了一个invisible索引。
SQL> create table tab as select * from user_objects;
Table created.
SQL> create index tab_idx1 on tab(object_name) invisible;
Index created.
SQL> col INDEX_NAME for a25
SQL> col TABLE_OWNER for a20
SQL> col TABLE_NAME for a20
SQL> col VISIBILITY for a25
SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY,STATUS from user_indexes where TABLE_NAME='TAB';
INDEX_NAME TABLE_OWNER TABLE_NAME VISIBILITY STATUS
---------- ----------- ---------- ----------- --------
TAB_IDX1 SCOTT TAB INVISIBLE测试优化器是否会忽略不可见索引
SQL> set autotrace traceonly
SQL> select * from tab where object_name='EMP';Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 1 | 190 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement (level=2)Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select /*+index(tab tab_idx1)*/ * from tab where object_name='EMP';
Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 1 | 190 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement (level=2)Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed从上面的两个执行计划看出，都没有走invisible index（即使加了hint也被忽略了），均走全表扫描。说明默认情况下，优化器会忽略invisible index,不使用invisible index。
测试优化器是否会使用可见索引
将invisible index修改为visible index，观察优化器会不会使用索引TAB_IDX1。
SQL> alter index tab_idx1 visible;
Index altered.
SQL> set autotrace traceonly
SQL> select * from tab where object_name='EMP';Execution Plan
----------------------------------------------------------
Plan hash value: 2166198891---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB | 1 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement (level=2)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1331 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedTAB_IDX1索引由invisible修改为visible后，执行计划走了TAB_IDX1索引。
测试参数optimizer_use_invisible_indexes对不可见索引的影响
下面测试参数optimizer_use_invisible_indexes对不可见索引的影响。这里仅在session级做测试。
将TAB_IDX1索引由visible index修改为invisible index。
SQL> alter index tab_idx1 invisible;
Index altered.
查看optimizer_use_invisible_indexes参数的值，默认为false。
SQL> show parameter optimizer_use_invisible_indexesNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE将参数值修改为true，优化器走了TAB_IDX1,使用了invisible index。
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select * from tab where object_name='EMP';Execution Plan
----------------------------------------------------------
Plan hash value: 2166198891---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB | 1 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement (level=2)Statistics
----------------------------------------------------------
9 recursive calls
2 db block gets
9 consistent gets
0 physical reads
0 redo size
1331 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off将参数值修改为false，优化器走了全表扫描，没有使用invisible index。
SQL> alter session set optimizer_use_invisible_indexes=false;
Session altered.
SQL> set autotrace traceonly
SQL>
SQL> select * from tab where object_name='EMP';Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 1 | 190 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP')
Note
-----
- dynamic sampling used for this statement (level=2)Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> set autotrace off
经过测试，发现在会话级将optimizer_use_invisible_indexes参数设置为true，优化器会使用invisible索引。在会话级将optimizer_use_invisible_indexes参数设置为false，优化器不会使用invisible索引。
不可见索引测试总结
通过测试，可知优化器默认会忽略invisible index,不使用invisible index，要想使用invisible index，需要将optimizer_use_invisible_indexes参数修改为true，才能使用。或者将invisible index修改visible index，这样也可以使用该索引。
随着invisible index的引入，给索引的维护管理工作带来了很多便利，所以日常运维时可以尝试使用invisible index，提高工作效率。
