主題:oracle虛拟索引詳解
背景:
DBA在日常維護管理資料庫進行低性能SQL分析時,有時候需要通過建立索引對SQL進行優化,但有些時候我們建立的索引是否能用到?
這個隻能建立以後才能看出效果,但是在實際工作中,特别是對大表建立索引對系統性能有很大影響,是以我們不得不避開業務高峰時段,但是有沒有一種辦法建立索引而不影響性能呢?
有,虛拟索引。
1 何為“虛拟索引” :
虛拟索引不是實體存在的,它并不會建立實際的索引段,隻是在資料字典中加了一個索引的記錄,使得優化器能夠意識到一個索引的存在,進而判斷是否使用該索引作為通路路徑。
作用僅僅是為了DBA作SQL優化時使用,DBA跟據虛拟索引的優化效果決定是否建立實體索引。
一、虛拟索引支援類型
虛拟索引支援B-TREE索引和BIT位圖索引,在CBO模式下ORACLE優化器會考慮虛拟索引,但是在RBO模式下需要添加hint才行
在10g以後使用RBO模式方法:alter session set optimizer_mode=rule;
二、虛拟索引建立文法
create index idx_wxw on t(object_id) nosegment;
alter session set "_use_nosegment_indexes"=true;
注意,建立虛拟索引後需要設定隐含參數"_use_nosegment_indexes"為true ORACLE才會選擇虛拟索引
三、虛拟索引特點
1.虛拟索引無法執行alter index選項
SQL> alter index idx_wxw rebuild
ERROR at line 1:
ORA-08114: can not alter a fake index
2. 使用資源回收筒特性的時候,虛拟索引必須顯式drop,才能建立同名的索引
SQL> create index idx_wxw on test(name)
ERROR at line 1:
ORA-00955: name is already used by an existing object
3. 不能建立和虛拟索引同名的實際索引
4. 可以建立和虛拟索引包含相同列但不同名的實際索引
5. 使用資源回收筒特性的時候,虛拟索引必須顯式drop,或者在drop table後purge table後,才能建立同名的索引
6. 虛拟索引分析并且有效,但是資料字典裡查不到結果,估計是oracle内部臨時儲存了分析結果