Oracle虚拟索引

简介:

从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,如需转载请自行联系原作者


相关文章
|
3月前
|
SQL Oracle 关系型数据库
Oracle-index索引解读
Oracle-index索引解读
74 0
|
2天前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
5月前
|
索引
Oracle-序列、索引和同义词
Oracle-序列、索引和同义词
26 0
|
2月前
|
SQL Oracle 关系型数据库
[Oracle]索引
[Oracle]索引
66 0
[Oracle]索引
|
5月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
179 0
|
6月前
|
存储 Oracle 关系型数据库
9-6 Oracle 管理索引
9-6 Oracle 管理索引
|
11月前
|
SQL Oracle 关系型数据库
Oracle-表分析和索引分析解读
Oracle-表分析和索引分析解读
137 0
|
11月前
|
存储 SQL Oracle
Oracle索引
Oracle索引
82 0

推荐镜像

更多