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