ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于虚拟索引(Virtual Index)

关于虚拟索引(Virtual Index)

原创 Linux操作系统 作者:NinGoo 时间:2019-04-21 08:36:05 0 删除 编辑

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

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

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

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


不同版本的虚拟索引的特性可能不一样,本文的例子执行环境为:

代码:
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

1.创建虚拟索引

代码:
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
.

2.使用虚拟索引

代码:
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
|
--------------------------------------------------------------------------

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

代码:
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
|
---------------------------------------------------------------------------------------

RBO无法使用虚拟索引

代码:
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
|
----------------------------------

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

代码:
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
|
---------------------------------------------------------------------------------------

3.虚拟索引的特性
无法执行alter index

代码:
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

不能创建和虚拟索引同名的实际索引

代码:
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

可以创建和虚拟索引包含相同列但不同名的实际索引

代码:
NING@ning>create index ix_test2 on test(id);
Index created
.

在10g使用回收站特性的时候,虚拟索引必须显式drop,或者在drop table后purge table后,才能创建同名的索引

代码:
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
.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50346/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    126
  • 访问量
    94125