oracle invisible index與unusable index的區別

不可見索引(Invisible Index)是ORACLE 11g引入的新特性。不可見索引是會被最佳化器忽略的不可見索引，除非在會話或系統級別上將OPTIMIZER_USE_INVISIBLE_INDEXES初始化引數顯式設定為TRUE。此引數的預設值是FALSE。

   （1）從11g開始，就有了invisible index

   （2）invisible index會被最佳化器所忽略，但是dml操作仍然會維護索引，批次提交資料不會提升效能

   （3）如果在session或者system級別使用引數OPTIMIZER_USE_INVISIBLE_INDEXES=true，那麼最佳化器會考慮使用invisible index

   （4）你可以使一個分割槽索引變成invisible index，但是你不能使單個分割槽變成invisible index，而其他分割槽visible

   （5）invisible index只是讓最佳化器不可見，索引段中的資料還是存在的，並且dml操作維護索引，所以visible index後，不需要重建索引

   （6）建立 invisible index    

            CREATE INDEX 索引名 ON 表名(列名)  INVISIBLE;

   （7） 現有索引變成invisible index

            ALTER INDEX 索引名 INVISIBLE;

虛擬索引是為了合理、科學新增索引而設計的，而不可見索引是為了合理、科學的刪除索引而設計的。

為什麼這樣說呢？ 因為DBA在維護索引時，我們經常會找出無用或低效的索引，並刪除這些索引，在生產環境下，刪除索引還是有一定風險的，即使ORACLE提供了監控索引使用情況的技術。例如，某些索引在監控週期中未被使用到，就會認為索引是無用的而被刪除。當結束監控後，後續又使用到了索引，可能就會對系統效能造成衝擊。這時，可能就會手忙腳亂的去找回索引定義語句、重建索引。11G之前，我們可以先不刪除索引，而將其修改為unusable。這樣的話，索引的定義並未刪除，只是索引不能再被使用也不會隨著表資料的更新而更新。當需要重新使用該索引時，需要用rebuild語句重建、然後更新統計資訊。對於一些大表來說，這個時間可能就非常長。在ORACLE 11g裡提供了一個新的特性來降低直接刪除索引或者禁用索引的風險，那就是索引不可見（Index Invisible）。我們可以將無用或低效的索引設定為不可見索引，當觀察一段時間後，發現其對系統效能並無任何影響，那麼就可以徹底刪除索引了。

當你在批次載入資料的時候，想要改變效能或者測試刪除索引後的效能，可以使用unusable index 或者是 invisible index

unusable index

（1）unusable index 是被最佳化器所忽略，並且不被dml操作維護。但是unusable index 可以改變批次載入的效能

（2）任何現有的索引變成unusable後，索引段都會被刪除

（3）由於unusable index 是不會被dml操作維護，索引變成unusable index後必須透過rebuild index 或者drop index  and create index

（4）當引數  SKIP_UNUSABLE_INDEXES =true時

dml操作時針對錶的操作，對於索引是不會維護的。

唯一約束使用的索引變成unusable，dml操作會報錯。

對於非分割槽索引，對於select 語句，最佳化器不考慮任何unusable index   ，除非明確指明使用 index hint強調      

對於分割槽索引，一個或者多個分割槽可以unusable，, the optimizer does not consider the index if it cannot determine at query compilation time if any of the index partitions can be pruned  ,除非明確指明使用 index hint強調  .

（5）當引數SKIP_UNUSABLE_INDEXES =FALSE

  當 unusable index 或者分割槽unusable index ，在dml操作更新索引時，會遇到錯誤

  當 select 語句遇到  unusable index 或者分割槽 unusable index 時，最佳化器不會考慮使用它，但是如果最佳化器使用了unusable index 就會報錯

 （6）建立分割槽unusable index   

        CREATE INDEX 索引名 ON 表名 (列名)

        LOCAL (PARTITION 分割槽名 UNUSABLE, PARTITION 分割槽名);

（7） 現有索引變成unusable索引             

        ALTER INDEX 索引名 UNUSABLE;

例項：

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from dba_objects;

Table created.

SQL> create index indtest on test(object_id);            

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> select * from test where object_id = 1;    

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1352592598

---------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |    98 |     2 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    98 |     2 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN     | INDTEST |     1 |       |     1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

  0  recursive calls

  0  db block gets

  2  consistent gets

  0  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed

SQL> alter index indtest invisible;

Index altered.

SQL> select * from test where object_id = 1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | |     1 |    98 |   216   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   216   (1)| 00:00:03 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

22  recursive calls

  0  db block gets

       1252  consistent gets

       1234  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  4  sorts (memory)

  0  sorts (disk)

  0  rows processed

SQL> select /*+ index(test indtest)*/ * from test where object_id = 1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | |     1 |    98 |   216   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   216   (1)| 00:00:03 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

  1  recursive calls

  0  db block gets

       1236  consistent gets

       1234  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> select * from test where object_id = 1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1352592598

---------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |    98 |     2 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    98 |     2 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN     | INDTEST |     1 |       |     1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1)

Statistics

----------------------------------------------------------

  1  recursive calls

  0  db block gets

  2  consistent gets

  0  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed

SQL>