本文共 3853 字，大约阅读时间需要 12 分钟。
从9.2版本开始Oracle引入了虚拟索引的概念，虚拟索引是一个“伪造”的索引，它的定义只存在数据字典中并有存在相关的索引段。虚拟索引是为了在不真正创建索引的情况下，验证如果使用索引sql执行计划是否改变，执行效率是否能得到提高。
本文在11.2.0.4版本中测试使用虚拟索引
1、创建测试表
| 1 2 3 4 5 6 7 8 9 | ZX@orcl> create table test_t as select * from dba_objects; Table created. ZX@orcl> select count (*) from test_t; COUNT (*) ---------- 86369 |
2、查看一个SQL的执行计划，由于没有创建索引，使用TABLE ACCESS FULL访问表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ZX@orcl> set autotrace traceonly explain ZX@orcl> select object_name from test_t where object_id=123; Execution Plan ---------------------------------------------------------- Plan hash value: 2946757696 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 344 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL | TEST_T | 14 | 1106 | 344 (1)| 00:00:05 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( "OBJECT_ID" =123) Note ----- - dynamic sampling used for this statement ( level =2) |
3、创建虚拟索引，数据字典中有这个索引的定义但是并没有实际创建这个索引段
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ZX@orcl> set autotrace off ZX@orcl> create index idx_virtual on test_t (object_id) nosegment; Index created. ZX@orcl> select object_name,object_type from user_objects where object_name= 'IDX_VIRTUAL' ; OBJECT_NAME OBJECT_TYPE -------------------------------------------------------------------------------------------------------------------------------- ------------------- IDX_VIRTUAL INDEX ZX@orcl> select segment_name,tablespace_name from user_segments where segment_name= 'IDX_VIRTUAL' ; no rows selected |
4、再次查看执行计划
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | ZX@orcl> set autotrace traceonly explain ZX@orcl> select object_name from test_t where object_id=123; Execution Plan ---------------------------------------------------------- Plan hash value: 2946757696 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 344 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL | TEST_T | 14 | 1106 | 344 (1)| 00:00:05 | ---------------------------------------------------------------------------- |
5、我们看到执行计划并没有使用上面创建的索引，要使用虚拟索引需要设置参数
| 1 2 3 | ZX@orcl> alter session set "_use_nosegment_indexes" = true ; Session altered. |
6、再次查看执行计划，可以看到执行计划选择了虚拟索引，而且时间也缩短了。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ZX@orcl> select object_name from test_t where object_id=123; Execution Plan ---------------------------------------------------------- Plan hash value: 1533029720 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_T | 14 | 1106 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_VIRTUAL | 315 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access( "OBJECT_ID" =123) Note ----- - dynamic sampling used for this statement ( level =2) |
从上面的执行计划可以看出创建这个索引会起到优化的效果，这个功能在大表建联合索引优化能起到很好的做作用，可以测试多个列组合哪个组合效果最好，而不需要实际每个组合都创建一个大索引。
7、删除虚拟索引
| 1 2 3 | ZX@orcl> drop index idx_virtual; Index dropped. |
MOS文档：Virtual Indexes (文档 ID 1401046.1)
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1890730，如需转载请自行联系原作者