Oracle虚拟索引

从9.2版本开始Oracle引入了虚拟索引的概念，虚拟索引是一个“伪造”的索引，它的定义只存在数据字典中并有存在相关的索引段。虚拟索引是为了在不真正创建索引的情况下，验证如果使用索引sql执行计划是否改变，执行效率是否能得到提高。

本文在11.2.0.4版本中测试使用虚拟索引

1、创建测试表

ZX@orcl>createtabletest_tasselect*fromdba_objects;

Tablecreated.

ZX@orcl>selectcount(*)fromtest_t;

COUNT(*)
----------
86369

2、查看一个SQL的执行计划，由于没有创建索引，使用TABLE ACCESS FULL访问表

ZX@orcl>setautotracetraceonlyexplain
ZX@orcl>selectobject_namefromtest_twhereobject_id=123;

ExecutionPlan
----------------------------------------------------------
Planhashvalue:2946757696

----------------------------------------------------------------------------
|Id|Operation	|Name|Rows|Bytes|Cost(%CPU)|Time	|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT|	|	14|1106|344(1)|00:00:05|
|*1|TABLEACCESSFULL|TEST_T|	14|1106|344(1)|00:00:05|
----------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

1-filter("OBJECT_ID"=123)

Note
-----
-dynamicsamplingusedforthisstatement(level=2)

3、创建虚拟索引，数据字典中有这个索引的定义但是并没有实际创建这个索引段

ZX@orcl>setautotraceoff
ZX@orcl>createindexidx_virtualontest_t(object_id)nosegment;

Indexcreated.

ZX@orcl>selectobject_name,object_typefromuser_objectswhereobject_name='IDX_VIRTUAL';

OBJECT_NAME															OBJECT_TYPE
---------------------------------------------------------------------------------------------------------------------------------------------------
IDX_VIRTUAL															INDEX

ZX@orcl>selectsegment_name,tablespace_namefromuser_segmentswheresegment_name='IDX_VIRTUAL';

norowsselected

4、再次查看执行计划

ZX@orcl>setautotracetraceonlyexplain
ZX@orcl>selectobject_namefromtest_twhereobject_id=123;

ExecutionPlan
----------------------------------------------------------
Planhashvalue:2946757696

----------------------------------------------------------------------------
|Id|Operation	|Name|Rows|Bytes|Cost(%CPU)|Time	|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT|	|	14|1106|344(1)|00:00:05|
|*1|TABLEACCESSFULL|TEST_T|	14|1106|344(1)|00:00:05|
----------------------------------------------------------------------------

5、我们看到执行计划并没有使用上面创建的索引，要使用虚拟索引需要设置参数

ZX@orcl>altersessionset"_use_nosegment_indexes"=true;

Sessionaltered.

6、再次查看执行计划，可以看到执行计划选择了虚拟索引，而且时间也缩短了。

ZX@orcl>selectobject_namefromtest_twhereobject_id=123;

ExecutionPlan
----------------------------------------------------------
Planhashvalue:1533029720

-------------------------------------------------------------------------------------------
|Id|Operation		|Name	|Rows|Bytes|Cost(%CPU)|Time	|
-------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT	|		|14|1106|	5(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|TEST_T	|14|1106|	5(0)|00:00:01|
|*2|INDEXRANGESCAN	|IDX_VIRTUAL|315|	|	1(0)|00:00:01|
-------------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

2-access("OBJECT_ID"=123)

Note
-----
-dynamicsamplingusedforthisstatement(level=2)

从上面的执行计划可以看出创建这个索引会起到优化的效果，这个功能在大表建联合索引优化能起到很好的做作用，可以测试多个列组合哪个组合效果最好，而不需要实际每个组合都创建一个大索引。

7、删除虚拟索引

ZX@orcl>dropindexidx_virtual;

Indexdropped.

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!