天天看點

oracle 虛拟索引 風險,oracle虛拟索引詳解

主題: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内部臨時儲存了分析結果