11g新特性: 索引不可見(Index Invisible)

lixianlinde發表於2012-12-26
索引維護是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_indexestrue(預設為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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章