关于虚拟索引(Virtual Index)

taming_wolf

又见大师佳作

NinGoo

最初由 ZALBB 发布
[B]

此东东有没有BUG，别满心欢喜的用了之后，却发现它的执行计划与实际索引的不一样？ [/B]

oracle EM中的tuning pack里的index advisor功能就是基于该特性的，有没有bug就不好说了，呵呵

毕竟，这给了我们一个不创建真实索引而判断是否需要建立索引的一个方法，利弊就需要在使用的时候权衡了

lfree

收藏一下，好像toad的sql tuning 可以使用这项功能，自己没有仔细研究。

谢谢！

lfree

>RBO使用hint可以使用虚拟索引
个人使用hint可以应该是cbo

NinGoo

最初由 lfree 发布
[B]>RBO使用hint可以使用虚拟索引
个人使用hint可以应该是cbo [/B]

RBO和CBO下使用hint都可以。只是因为RBO如果不使用hint则无效，所以这里特别列出来了

bluemoon0083

圈圈同志厉害的！
收藏了

lfree

昨天跟踪了toad带的sql tuning，实际上也是使用同样的方式。
实际上使用这种方式对于优化还是很有好处的。

看来自己学习还是不够认真。

lfree

如果建立后忘记删除，如何寻找呢？
好像仅仅可以通过这个确定。

select owner||object_name from dba_objects where object_type='INDEX'
minus
select owner||index_name from dba_indexes

NinGoo

通过sql trace可以知道，虚拟索引只在三张表中插了数据：obj$,icol$和ind$，由于不创建实际的segment，所以seg$不会有记录

所以通过dba_objects minus dba_indexes来查找是可以的，dba_indexes中的数据是已经关联了seg$的结果

fjliangrq

最初由 NinGoo 发布
[B]在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。

虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。当然，实际上最终查询的访问路径是不会使用该虚拟索引的。

所以，虚拟索引的用处就是用来判断一个索引对于sql的执行计划的影响，尤其是对整个数据库的影响，从而判断是否需要创建物理索引。

oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个nosegment关键字即可，B*Tree index和bitmap index都可以。

不同版本的虚拟索引的特性可能不一样，本文的例子执行环境为：
[php]
NING@ning>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
--
[/php]
1.创建虚拟索引
[php]
NING@ning>create table test(id int,name varchar2(30));

Table created.

NING@ning>insert into test select rownum,object_name from all_objects where rownum<1001;

1000 rows created.

NING@ning>commit;

Commit complete.

NING@ning>create unique index ix_test on test(id) nosegment;

Index created.

NING@ning>analyze table test compute statistics;

Table analyzed.
--
[/php]
2.使用虚拟索引
[php]
NING@ning>explain plan for select * from test where id=1;

Explained.

NING@ning>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 |    17 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    17 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
[/php]
必须设置隐含参数"_use_nosegment_indexes"=true（默认为false)后，CBO才能使用虚拟索引ix_test
[php]
NING@ning>alter session set "_use_nosegment_indexes"=true;

Session altered.

NING@ning>select * from table(dbms_xplan.display());

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

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    17 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IX_TEST |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
[/php]
RBO无法使用虚拟索引
[php]
NING@ning>alter session set optimizer_mode=rule;

Session altered.

NING@ning>explain plan for select * from test where id=1;

Explained.

NING@ning>select * from table(dbms_xplan.display());

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

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------
[/php]
RBO使用hint可以使用虚拟索引
[php]
NING@ning>explain plan for select /*+ index(test,ix_test)*/* from test where id=1;

Explained.

NING@ning>select * from table(dbms_xplan.display());

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

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    17 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IX_TEST |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
[/php]
3.虚拟索引的特性

无法执行alter index
[php]
NING@ning>alter index ix_test rebuild;
alter index ix_test rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

NING@ning>alter index ix_test rename to ix_test2;
alter index ix_test rename to ix_test2
*
ERROR at line 1:
ORA-08114: can not alter a fake index
--
[/php]
不能创建和虚拟索引同名的实际索引
[php]
NING@ning>create index ix_test on test(name);
create index ix_test on test(name)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
--
[/php]
可以创建和虚拟索引包含相同列但不同名的实际索引
[php]
NING@ning>create index ix_test2 on test(id);

Index created.
--
[/php]
在10g使用回收站特性的时候，虚拟索引必须显式drop，或者在drop table后purge table后，才能创建同名的索引
[php]
NING@ning>drop table test;

Table dropped.

NING@ning>create unique index ix_test on test2(id);
create unique index ix_test on test2(id)
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object

NING@ning>drop index ix_test;
drop index ix_test
           *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

NING@ning>purge table test;

Table purged.

NING@ning>create unique index ix_test on test2(id);

Index created.
--
[/php] [/B]

NING@ning>create unique index ix_test on test(id) nosegment;
这个是建立虚拟索引的关键字吗?
请问是针对11G的吗