Oracle 11g: Invisible Indexes

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         2  for i in 1 .. 10000 loop  3  insert into ii_tab values (i);  4  end loop;  5  commit;  6  end;  7  /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 onSQL> select * from ii_tab where id=9999;        ID----------      9999Execution 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):---------------------------------------------------   1 - filter("ID"=9999)Statistics----------------------------------------------------------         11  recursive calls          0  db block gets         43  consistent gets          0  physical reads          0  redo size        521  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          5  sorts (memory)          0  sorts (disk)          1  rows processedSQL> alter session set optimizer_use_invisible_indexes=true;Session altered.SQL> select * from ii_tab where id=9999;        ID----------      9999Execution 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):---------------------------------------------------   1 - access("ID"=9999)Statistics----------------------------------------------------------         12  recursive calls          0  db block gets         27  consistent gets          0  physical reads          0  redo size        521  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          6  sorts (memory)          0  sorts (disk)          1  rows processedSQL> alter session set optimizer_use_invisible_indexes=false;Session altered.

可以看到即使設定索引為invisible，當optimizer_use_invisible_indexes為true的時候 最佳化器仍然會走索引而非全表。

對比一下 ALTER INDEX ... UNUSABLE, 最佳化器不會使用UNUSABLE索引，需要 REBUILD