程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 11g新特性:索引不可見

Oracle 11g新特性:索引不可見

編輯:Oracle數據庫基礎
索引維護是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 /*+ index(t_test1 T_TEST1_IDX) */ * 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 the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved