不可見索引概念 不可見索引(Invisible Index)是ORACLE 11g引入的新特性。不可見索引是會被優化器忽略的不可見索引，除非在會話或系統級別上將OPTIMIZER_USE_INVISIBLE_INDEXES初始化參數顯式設置為TRUE。此參數的預設值是FALSE。如果是虛擬索引是為了... ...
不可見索引概念
不可見索引(Invisible Index)是ORACLE 11g引入的新特性。不可見索引是會被優化器忽略的不可見索引，除非在會話或系統級別上將OPTIMIZER_USE_INVISIBLE_INDEXES初始化參數顯式設置為TRUE。此參數的預設值是FALSE。如果是虛擬索引是為了合理、科學新增索引而設計的，那麼不可見索引就是為了合理、科學的刪除索引而設計的。為什麼這樣說呢？ 因為DBA在維護索引時，我們經常會找出無用或低效的索引，並刪除這些索引，在生產環境下，刪除索引還是有一定風險的，即使ORACLE提供了監控索引使用情況的技術。例如，某些索引可能只是在一些周期的作業中被使用到，而如果監控周期沒有覆蓋到這些作業的觸發點，就會認為索引是無用的而被刪除。當作業啟動後，可能就會對系統性能造成衝擊。這時，可能就會手忙腳亂的去找回索引定義語句、重建索引。11G之前，我們可以先不刪除索引，而將其修改為unusable。這樣的話，索引的定義並未刪除，只是索引不能再被使用也不會隨著表數據的更新而更新。當需要重新使用該索引時，需要用rebuild語句重建、然後更新統計信息。對於一些大表來說，這個時間可能就非常長。在ORACLE 11g里提供了一個新的特性來降低直接刪除索引或者禁用索引的風險，那就是索引不可見（Index Invisible）。我們可以將無用或低效的索引設置為不可見索引，當觀察一段時間後，發現其對系統性能並無任何影響，那麼就可以徹底刪除索引了。
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer and the user unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.The default value for this parameter is FALSE.
Using invisible indexes, you can do the following:
1.Test the removal of an index before dropping it.
2.Use temporary index structures for certain operations or modules of an application without affecting the overall application.
Unlike unusable indexes, an invisible index is maintained during DML statements.
不可見索引測試
創建測試表TEST，在表上新建不可見索引IDX_TEST_ID，不可見索引可以從欄位VISIBILITY這個欄位來區別，如下所示：
SQL> create table test
2 as3 select * from dba_objects;
Table created.SQL> create index idx_test_id on test(object_id) invisible;
Index created.SQL> select index_name, status,visibility from dba_indexes where index_name=upper('idx_test_id');
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------IDX_TEST_ID VALID INVISIBLESQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_use_invisible_indexes boolean FALSESQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>'TEST',CASCADE => TRUE);
PL/SQL procedure successfully completed.
如下所示，優化器是“看不見”這個索引的，即使使用提示hint強制其走這個索引。優化器還是不會走索引掃描
SQL> set autotrace traceonly;
SQL> select * from test where object_id=12;
Execution Plan----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 97 | 282 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_ID"=12)Statistics---------------------------------------------------------- 424 recursive calls0 db block gets
1094 consistent gets
0 physical reads 0 redo size1604 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk) 1 rows processedSQL> select /*+ index(text, idx_test_id) */ * from test where object_id=12;
Execution Plan----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 97 | 282 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_ID"=12)Statistics---------------------------------------------------------- 1 recursive calls0 db block gets
1036 consistent gets
0 physical reads 0 redo size1604 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
