索引的不可视

使用alter index index_name invisible可以将索引设置成为不可视（失效），但索引的维护工作还会继续。

例子：

--创建试验表

create table t as select * from dba_objects;

--创建索引(不可视索引)

create index t_ind on t(object_id) invisible;

--查看执行计划

set autotrace on

select * from t where object_id = 2;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   115 |   426   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   115 |   426   (1)| 00:00:01 |
--------------------------------------------------------------------------

--将索引恢复为可视

alter index t_ind visible;

select * from t where object_id = 2;

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

--也可以使用no_index的方法屏蔽索引

select /*+ no_index(t t_ind) */ * from t where object_id = 2;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   115 |   426   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   115 |   426   (1)| 00:00:01 |
--------------------------------------------------------------------------