Oracle中怎么实现虚拟索引，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
1.创建一个测试表test
SQL> create table test as select * from dba_objects; Table created.2.从表test查询object_name等于standard的记录
SQL> select * from test where object_name='STANDARD'; OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ----------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 888 PACKAGE 19-APR-10 19-APR-10 2003-04-18:00:00:00 VALID N N N OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 889 PACKAGE BODY 19-APR-10 19-APR-10 2010-04-19:10:22:58 VALID N N N3.查询上面查询的执行计划
SQL> set autotrace traceonly explain SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement4.在表test的object_name列上创建一个虚拟索引
SQL> create index test_index on test(object_name) nosegment;Index created.为了创建虚拟索引必须在create index语句中指定nosegment子句，并且不会创建索引段。
5.来验证虚拟索引不会创建索引段
SQL> set autotrace off SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX'; no rows selected SQL> col OBJECT_NAME format a20; SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX'; OBJECT_NAME OBJECT_TYPE -------------------- ------------------- TEST_INDEX INDEX从上面的结果可以看到索引对象已经创建，但没有创建索引段。
6.重新执行sql查看创建的虚拟索引是否被使用
SQL> set autotrace traceonly explainSQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用。
7.为了能使用所创建的虚拟索引，需要将_USE_NOSEGMENT_INDEXES设置为true
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;Session altered.8.重新执行sql查看创建的虚拟索引是否被使用
SQL> set long 900SQL> set linesize 900 SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 2627321457 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | 238 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement看完上述内容，你们掌握Oracle中怎么实现虚拟索引的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！
