oracle 11g 允许将索引标记为invisible. oracle像维护其他索引一样维护 invisible index ，但是默认invisible index不能被优化器
oracle 11g 允许将索引标记为invisible. oracle像维护其他索引一样维护 invisible index ，但是默认invisible index不能被优化器使用，，除非显式设置 optimizer_use_invisible_indexes=true(可以alter system/session).可以在创建索引的时候指定 invisible关键字或 alter index命令来invisible一个索引。
create index idx_name on table_name(column_name) invisible;
alter index idx_name invisible;
alter index idx_name visible;
demo:
sql> create table ii_tab( id number);
table created.
sql> begin     
  for i in 1 .. 10000 loop
  insert into ii_tab values (i);
  end loop;
  commit;
  end;
  /
pl/sql procedure successfully completed.
sql> create index ii_tab_id on ii_tab(id) invisible;
index created.
sql> exec dbms_stats.gather_table_stats(user,'ii_tab',cascade=>true);
pl/sql procedure successfully completed.
sql> set autotrace on
sql> select * from ii_tab where id=9999;
        id
----------
execution plan
----------------------------------------------------------
plan hash value: 2057286804
----------------------------------------------------------------------------
| id  | operation        | name  | rows  | bytes | cost (%cpu)| time    |
----------------------------------------------------------------------------
|  0 | select statement  |        |    1 |    4 |    7  (0)| 00:00:01 |
|*  1 |  table access full| ii_tab |    1 |    4 |    7  (0)| 00:00:01 |
----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
 - filter(id=9999)
statistics
----------------------------------------------------------
  recursive calls
  db block gets
  consistent gets
  physical reads
  redo size
  bytes sent via sql*net to client
  bytes received via sql*net from client
  sql*net roundtrips to/from client
  sorts (memory)
  sorts (disk)
  rows processed
sql> alter session set optimizer_use_invisible_indexes=true;
session altered.
sql> select * from ii_tab where id=9999;
        id
----------
execution plan
----------------------------------------------------------
plan hash value: 81730945
------------------------------------------------------------------------------
| id  | operation        | name      | rows  | bytes | cost (%cpu)| time    |
------------------------------------------------------------------------------
|  0 | select statement |          |    1 |    4 |    1  (0)| 00:00:01 |
|*  1 |  index range scan| ii_tab_id |    1 |    4 |    1  (0)| 00:00:01 |
------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
 - access(id=9999)
statistics
----------------------------------------------------------
  recursive calls
  db block gets
  consistent gets
  physical reads
  redo size
  bytes sent via sql*net to client
  bytes received via sql*net from client
  sql*net roundtrips to/from client
  sorts (memory)
  sorts (disk)
  rows processed
sql> alter session set optimizer_use_invisible_indexes=false;
session altered.
可以看到即使设置索引为invisible，当optimizer_use_invisible_indexes为true的时候 优化器仍然会走索引而非全表。
对比一下 alter index ... unusable, 优化器不会使用unusable索引，需要 rebuild