你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle虚拟索引

[日期:2017-01-10] 来源:Linux社区  作者:hbxztc [字体: ]

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

1、创建测试表
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访问表

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、创建虚拟索引,数据字典中有这个索引的定义但是并没有实际创建这个索引段
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、再次查看执行计划
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、我们看到执行计划并没有使用上面创建的索引,要使用虚拟索引需要设置参数
ZX@orcl> alter session set "_use_nosegment_indexes"=true;
 
Session altered.

6、再次查看执行计划,可以看到执行计划选择了虚拟索引,而且时间也缩短了。
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、删除虚拟索引
ZX@orcl> drop index idx_virtual;
 
Index dropped.

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

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-01/139419.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款