在oracle11.2中，数据库允许关闭索引，使其不可见，但是索引上面的维护工作还是继续的，包括任何DML操作。当如何确定一个索引是否真正地被需要时，就可以通过设置为不可见来判断SQL执行计划。
语法：

通过测试来说明下不可见索引的使用，通过是user_ind_columns 确定表EMP2 没有任何索引，其中数据共计108条
点击(此处)折叠或打开

SQL> select table_name,index_name,column_name,column_position from user_ind_columns where table_name = 'EMP2';
no rows selected
SQL> select count(*) from emp2;
COUNT(*)
----------
108

对表EMP2的job_id 列创建不可见索引,并确定JOB_ID的可以选择性

点击(此处)折叠或打开

SQL> create index idx_emp2_jobid on hr.emp2(job_id) invisible;
Index created.
SQL> select table_name,index_name,column_name,column_position from user_ind_columns where table_name = 'EMP2';
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------
EMP2 IDX_EMP2_JOBID JOB_ID 1
SQL> select distinct job_id from hr.emp2;

JOB_ID
----------
IT_PROG
AD_ASST
SA_MAN
MK_REP
AD_PRES
SA_REP
AC_MGR
AC_ACCOUNT
ST_MAN
PU_MAN
SH_CLERK
AD_VP
FI_ACCOUNT
MK_MAN
PR_REP
FI_MGR
PU_CLERK
HR_REP
ST_CLERK

19 rows selected.

查询执行计划1，未使用新建索引
点击(此处)折叠或打开

通过使用hints use_invisible_indexes提示查看执行计划，使用了索引IDX_EMP2_JOBID
点击(此处)折叠或打开

SQL> select /*+ use_invisible_indexes */ count(*) from hr.emp2 where job_id = 'MK_REP';
Execution Plan
----------------------------------------------------------
Plan hash value: 3396120133
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IDX_EMP2_JOBID | 6 | 54 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='MK_REP')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

最后把索引IDX_EMP2_JOBID 设置为可见，查看执行计划使用了索引。

点击(此处)折叠或打开

SQL> alter index idx_emp2_jobid visible;
Index altered.
SQL> select count(*) from hr.emp2 where job_id = 'MK_REP';
Execution Plan
----------------------------------------------------------
Plan hash value: 3396120133
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IDX_EMP2_JOBID | 6 | 54 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='MK_REP')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

通过使用不可见索引，临时“关闭”他们来测试查询没有他们时候的执行性能。由于不可见索引一直在维护的，如果需要他们就可以轻易把他们“打开”，使他们可见。这是使用不可见索引的要诀。

ITPub博客

如何使用不可见索引测试查询性能以及不可见索引特性