Oracle虛拟索引

從9.2版本開始Oracle引入了虛拟索引的概念，虛拟索引是一個“僞造”的索引，它的定義隻存在資料字典中并有存在相關的索引段。虛拟索引是為了在不真正建立索引的情況下，驗證如果使用索引sql執行計劃是否改變，執行效率是否能得到提高。

本文在11.2.0.4版本中測試使用虛拟索引

1、建立測試表

<code>ZX@orcl&gt; </code><code>create</code> <code>table</code> <code>test_t </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects;</code>

<code>Table</code> <code>created.</code>

<code>ZX@orcl&gt; </code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>test_t;</code>

<code>  </code><code>COUNT</code><code>(*)</code>

<code>----------</code>

<code>     </code><code>86369</code>

2、檢視一個SQL的執行計劃，由于沒有建立索引，使用TABLE ACCESS FULL通路表

<code>ZX@orcl&gt; </code><code>set</code> <code>autotrace traceonly explain</code>

<code>ZX@orcl&gt; </code><code>select</code> <code>object_name </code><code>from</code> <code>test_t </code><code>where</code> <code>object_id=123;</code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>Plan hash value: 2946757696</code>

<code>----------------------------------------------------------------------------</code>

<code>| Id  | Operation     | </code><code>Name</code>   <code>| </code><code>Rows</code>  <code>| Bytes | Cost (%CPU)| </code><code>Time</code>       <code>|</code>

<code>|   0 | </code><code>SELECT</code> <code>STATEMENT  |    | 14 |  1106 |   344   (1)| 00:00:05 |</code>

<code>|*  1 |  </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| TEST_T |   14 |  1106 |   344   (1)| 00:00:05 |</code>

<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>

<code>---------------------------------------------------</code>

<code>   </code><code>1 - filter(</code><code>"OBJECT_ID"</code><code>=123)</code>

<code>Note</code>

<code>-----</code>

<code>   </code><code>- </code><code>dynamic</code> <code>sampling used </code><code>for</code> <code>this statement (</code><code>level</code><code>=2)</code>

3、建立虛拟索引，資料字典中有這個索引的定義但是并沒有實際建立這個索引段

<code>ZX@orcl&gt; </code><code>set</code> <code>autotrace </code><code>off</code>

<code>ZX@orcl&gt; </code><code>create</code> <code>index</code> <code>idx_virtual </code><code>on</code> <code>test_t (object_id) nosegment;</code>

<code>Index</code> <code>created.</code>

<code>ZX@orcl&gt; </code><code>select</code> <code>object_name,object_type </code><code>from</code> <code>user_objects </code><code>where</code> <code>object_name=</code><code>'IDX_VIRTUAL'</code><code>;</code>

<code>OBJECT_NAME                                                          OBJECT_TYPE</code>

<code>-------------------------------------------------------------------------------------------------------------------------------- -------------------</code>

<code>IDX_VIRTUAL                                                          </code><code>INDEX</code>

<code>ZX@orcl&gt; </code><code>select</code> <code>segment_name,tablespace_name </code><code>from</code> <code>user_segments </code><code>where</code> <code>segment_name=</code><code>'IDX_VIRTUAL'</code><code>;</code>

<code>no</code> <code>rows</code> <code>selected</code>

4、再次檢視執行計劃

5、我們看到執行計劃并沒有使用上面建立的索引，要使用虛拟索引需要設定參數

<code>ZX@orcl&gt; </code><code>alter</code> <code>session </code><code>set</code> <code>"_use_nosegment_indexes"</code><code>=</code><code>true</code><code>;</code>

<code>Session altered.</code>

6、再次檢視執行計劃，可以看到執行計劃選擇了虛拟索引，而且時間也縮短了。

<code>Plan hash value: 1533029720</code>

<code>-------------------------------------------------------------------------------------------</code>

<code>| Id  | Operation           | </code><code>Name</code>   <code>| </code><code>Rows</code>  <code>| Bytes | Cost (%CPU)| </code><code>Time</code>   <code>|</code>

<code>|   0 | </code><code>SELECT</code> <code>STATEMENT      |         |    14 |  1106 |   5   (0)| 00:00:01 |</code>

<code>|   1 |  </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| TEST_T   |    14 |  1106 |   5   (0)| 00:00:01 |</code>

<code>|*  2 |   </code><code>INDEX</code> <code>RANGE SCAN      | IDX_VIRTUAL |   315 |     |   1   (0)| 00:00:01 |</code>

<code>   </code><code>2 - access(</code><code>"OBJECT_ID"</code><code>=123)</code>

從上面的執行計劃可以看出建立這個索引會起到優化的效果，這個功能在大表建聯合索引優化能起到很好的做作用，可以測試多個列組合哪個組合效果最好，而不需要實際每個組合都建立一個大索引。

7、删除虛拟索引

<code>ZX@orcl&gt; </code><code>drop</code> <code>index</code> <code>idx_virtual;</code>

<code>Index</code> <code>dropped.</code>

MOS文檔：Virtual Indexes (文檔 ID 1401046.1)

<b>     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1890730</b><b>，如需轉載請自行聯系原作者</b>