虚拟索引(virtual index)-evdblue-ChinaUnix博客

evdblue的ChinaUnix博客evdblue.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

evdblue

博客访问： 104960
博文数量： 13
博客积分： 470
博客等级：一等列兵
技术积分： 170
用户组：普通用户
注册时间： 2011-09-28 16:09

文章分类

全部博文（13）

文章存档

2011年（13）

我的朋友

相关博文

虚拟索引(virtual index)

分类： Oracle

2011-12-06 17:28:48

虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。当然，实际上最终查询的访问路径是不会使用该虚拟索引的。
所以，虚拟索引的用处就是用来判断一个索引对于sql的执行计划的影响，尤其是对整个数据库的影响，从而判断是否需要创建物理索引。
oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个nosegment关键字即可，B*Tree index和bitmap index都可以。

本文例子执行环境：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
１、创建虚拟索引

SQL> create table test(id int ,name varchar2(30));

表已创建。

SQL> insert into test select rownum,object_name from all_objects where rownum<2001;

创建2000行。

SQL> create unique index ix_test on test(id) nosegment;

索引已创建。

SQL> analyze table test compute statistics;

表已分析。

2、使用虚拟索引

SQL> explain plan for select * from test where id=1;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    19 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |     1 |    19 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=1)

已选择13行。

必须设置隐含参数"_use_nosegment_indexes"=true（默认为false)后，CBO才能使用虚拟索引ix_test

SQL> alter session set "_use_nosegment_indexes"=true;

会话已更改。

SQL> explain plan for select * from test where id=1;

已解释。

SQL> SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 166686173

--------------------------------------------------------------------------------
-------

--------------------------------------------------------------------------------
-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:
00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 19 | 2 (0)| 00:
00:01 |

|* 2 | INDEX UNIQUE SCAN | IX_TEST | 1 | | 1 (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

已选择14行。

RBO无法使用虚拟索引

SQL> alter session set optimizer_mode=rule;

会话已更改。

SQL> explain plan for select * from test where id=1;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=1)

Note
-----
- rule based optimizer used (consider using cbo)

已选择17行。

RBO使用hint可以使用虚拟索引

SQL> explain plan for select /*+ index(test,ix_test)*/* from test where id=1;