Oracle中怎么实现虚拟索引，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1.创建一个测试表test

SQL>createtabletestasselect*fromdba_objects;Tablecreated.

2.从表test查询object_name等于standard的记录

SQL>select*fromtestwhereobject_name='STANDARD';OWNER------------------------------OBJECT_NAME--------------------------------------------------------------------SUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPE-----------------------------------------------------------------CREATEDLAST_DDL_TIMTIMESTAMPSTATUSTGS-----------------------------------------------------SYSSTANDARD888PACKAGE19-APR-1019-APR-102003-04-18:00:00:00VALIDNNNOWNER------------------------------OBJECT_NAME--------------------------------------------------------------------SUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPE-------------------------------------------------------------------------CREATEDLAST_DDL_TIMTIMESTAMPSTATUSTGS-----------------------------------------------------SYSSTANDARD889PACKAGEBODY19-APR-1019-APR-102010-04-19:10:22:58VALIDNNN

3.查询上面查询的执行计划

SQL>setautotracetraceonlyexplainSQL>select*fromtestwhereobject_name='STANDARD';ExecutionPlan----------------------------------------------------------Planhashvalue:1357081020--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||8|1416|155(1)|00:00:02||*1|TABLEACCESSFULL|TEST|8|1416|155(1)|00:00:02|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OBJECT_NAME"='STANDARD')Note------dynamicsamplingusedforthisstatement

4.在表test的object_name列上创建一个虚拟索引

SQL>createindextest_indexontest(object_name)nosegment;Indexcreated.

为了创建虚拟索引必须在create index语句中指定nosegment子句，并且不会创建索引段。

5.来验证虚拟索引不会创建索引段

SQL>setautotraceoffSQL>selectindex_namefromdba_indexeswheretable_name='TEST'andindex_name='TEST_INDEX';norowsselectedSQL>colOBJECT_NAMEformata20;SQL>selectobject_name,object_typefromdba_objectswhereobject_name='TEST_INDEX';OBJECT_NAMEOBJECT_TYPE---------------------------------------TEST_INDEXINDEX

从上面的结果可以看到索引对象已经创建，但没有创建索引段。

6.重新执行sql查看创建的虚拟索引是否被使用

SQL>setautotracetraceonlyexplainSQL>select*fromtestwhereobject_name='STANDARD';ExecutionPlan----------------------------------------------------------Planhashvalue:1357081020--------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------|0|SELECTSTATEMENT||8|1416|155(1)|00:00:02||*1|TABLEACCESSFULL|TEST|8|1416|155(1)|00:00:02|--------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OBJECT_NAME"='STANDARD')Note------dynamicsamplingusedforthisstatement

从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用。

7.为了能使用所创建的虚拟索引，需要将_USE_NOSEGMENT_INDEXES设置为true

SQL>altersessionset"_USE_NOSEGMENT_INDEXES"=true;Sessionaltered.

8.重新执行sql查看创建的虚拟索引是否被使用

SQL>setlong900SQL>setlinesize900SQL>select*fromtestwhereobject_name='STANDARD';ExecutionPlan----------------------------------------------------------Planhashvalue:2627321457--------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------|0|SELECTSTATEMENT||8|1416|5(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TEST|8|1416|5(0)|00:00:01||*2|INDEXRANGESCAN|TEST_INDEX|238||1(0)|00:00:01|--------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OBJECT_NAME"='STANDARD')Note------dynamicsamplingusedforthisstatement

看完上述内容，你们掌握Oracle中怎么实现虚拟索引的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！