11g新特性: 索引不可見（Index Invisible）

索引維護是DBA的一項重要工作。當一個系統執行很長一段時間，經過需求變更、結構設計變化後，系統中就可能會存在一些不會被使用的索引，或者使用效率很低的索引。這些索引的存在，不僅佔用系統空間，而且會降低事務效率，增加系統的waits。因此，我們需要找出那些無用或低效索引的索引並刪除它們（找出無用索引可以通過索引監控 的方法）。    但是，直接刪除索引還是存在一定風險的。例如，某些索引可能只是在一些週期的作業中被使用到，而如果監控週期沒有覆蓋到這些作業的觸發點，就會認為索引是無用的而被刪除。當作業啟動後，可能就會對系統效能造成衝擊。這時，可能就會手忙腳亂的去找回索引定義語句、重建索引。
     11G之前，我們可以先不刪除索引，而將其修改為unusable。這樣的話，索引的定義並未刪除，只是索引不能再被使用也不會隨著表資料的更新而更新。當需要重新使用該索引時，需要用rebuild語句重建、然後更新統計資訊。對於一些大表來說，這個時間可能就非常長。
    在11g裡，Oracle提供了一個新的特性來降低直接刪除索引或者禁用索引的風險，那就是索引不可見（Index Invisible）。我們可以在建立索引時指定invisible屬性或者用alter語句來修改索引為invisible。
SQL程式碼

• 
  SQL> create index t_test1_idx on t_test1(table_name) invisible;   
•   
  Index created.   
•   
  SQL> alter index t_test1_idx invisible;   
•   
• Index altered.  
  

    當索引被設為不可見後，實際上就是指該索引對於優化器不可見，而索引的正常更新並不受影響——即表在增、刪、改時，索引也會被更新。只是當優化器在選擇查詢計劃時會“無視”該索引（無論是CBO或RBO）：
SQL程式碼

• 
  SQL> create table t_test1 as select * from dba_tables;   
•   
  Table created.   
•   
  SQL> create index t_test1_idx on t_test1(table_name);   
•   
  Index created.   
•   
  SQL> analyze table t_test1 compute statistics for table for all indexes;   
•   
  Table analyzed.   
•   
  SQL> set autot trace exp   
•   
  SQL> select * from t_test1 where table_name like 'HR%';   
•   
  no rows selected   
•   
    
• Execution Plan   
  ----------------------------------------------------------   
• Plan hash value: 3466041839   
    
• -------------------------------------------------------------------------------------------   
  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
• -------------------------------------------------------------------------------------------   
  |   0 | SELECT STATEMENT            |             |   126 | 27468 |    14   (0)| 00:00:01 |  
• |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |   126 | 27468 |    14   (0)| 00:00:01 |  
  |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |    23 |       |     2   (0)| 00:00:01 |  
• -------------------------------------------------------------------------------------------   
    
• Predicate Information (identified by operation id):   
  ---------------------------------------------------   
•   
     2 - access("TABLE_NAME" LIKE 'HR%')   
•        filter("TABLE_NAME" LIKE 'HR%')   
    
• SQL> alter index t_test1_idx invisible;   
    
• Index altered.   
    
• SQL> select * from t_test1 where table_name like 'HR%';   
    
• no rows selected   
    
•   
  Execution Plan   
• ----------------------------------------------------------   
  Plan hash value: 1883417357   
•   
  -----------------------------------------------------------------------------   
• | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   
  -----------------------------------------------------------------------------   
• |   0 | SELECT STATEMENT  |         |   126 | 27468 |    25   (0)| 00:00:01 |   
  |*  1 |  TABLE ACCESS FULL| T_TEST1 |   126 | 27468 |    25   (0)| 00:00:01 |   
• -----------------------------------------------------------------------------   
    
• Predicate Information (identified by operation id):   
  ---------------------------------------------------   
•   
•    1 - filter("TABLE_NAME" LIKE 'HR%')   
  

    如果我們需要重新使該所有有效，只需要再將其改為visible就可以了——這個過程不是重建，僅僅是修改索引的一個屬性，非常快！
SQL程式碼

• 
  SQL> set timing on  
• SQL> alter index t_test1_idx visible;   
    
• Index altered.   
    
• Elapsed: 00:00:00.01  
  

    當然，當索引被設為不可見時，並非完全不可用。可以通過修改引數optimizer_use_invisible_indexes為true（預設為false，system級別和session級別都可以）：
SQL程式碼

• 
  SQL> select index_name, visibility from user_indexes where index_name='T_TEST1_IDX';   
•   
  INDEX_NAME                     VISIBILIT   
• ------------------------------ ---------   
  T_TEST1_IDX                    INVISIBLE   
•   
  SQL>   
•   
  SQL> alter session set optimizer_use_invisible_indexes=true;   
•   
  Session altered.   
•   
  SQL> set autot trace exp   
• SQL> select * from t_test1 where table_name like 'HR%';   
    
• Execution Plan   
  ----------------------------------------------------------   
• Plan hash value: 3466041839   
    
• -------------------------------------------------------------------------------------------   
  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
• -------------------------------------------------------------------------------------------   
  |   0 | SELECT STATEMENT            |             |   126 | 27468 |    14   (0)| 00:00:01 |  
• |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |   126 | 27468 |    14   (0)| 00:00:01 |  
  |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |    23 |       |     2   (0)| 00:00:01 |  
• -------------------------------------------------------------------------------------------   
    
• Predicate Information (identified by operation id):   
  ---------------------------------------------------   
•   
     2 - access("TABLE_NAME" LIKE 'HR%')   
•        filter("TABLE_NAME" LIKE 'HR%')  
  

    這裡還需要指出一點，當索引不可見時，只能通過上面的引數使之在查詢計劃中可用，即使通過HINT也無法改變。
SQL程式碼

• 
  SQL> select * from t_test1 t where table_name like 'HR%';   
• Elapsed: 00:00:00.00   
    
• Execution Plan   
  ----------------------------------------------------------   
• Plan hash value: 1883417357   
    
• -----------------------------------------------------------------------------   
  | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   
• -----------------------------------------------------------------------------   
  |   0 | SELECT STATEMENT  |         |   126 | 27468 |    25   (0)| 00:00:01 |   
• |*  1 |  TABLE ACCESS FULL| T_TEST1 |   126 | 27468 |    25   (0)| 00:00:01 |   
  -----------------------------------------------------------------------------   
•   
  Predicate Information (identified by operation id):   
• ---------------------------------------------------   
    
•    1 - filter("TABLE_NAME" LIKE 'HR%')  
  

    在官方文件上對這一特性的說明中也只有提到該引數才起作用：

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set theOPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

--- The End ---

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24570973/viewspace-751675/，如需轉載，請註明出處，否則將追究法律責任。