使用虚拟索引进行数据库优化

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

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

所以，虚拟索引的用处就是用来判断一个索引对于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 .

查找系统中已经存在的虚拟索引：

SELECT index_owner , index_name  
FROM dba_ind_columns
WHERE index_name NOT LIKE ' BIN$% '
MINUS
SELECT owner , index_name
FROM dba_indexes ;

虚拟索引分析并且有效，但是数据字典里查不到结果，估计是oracle内部临时保存了分析结果

々上善若水々 2008-04-29 10:20 发表评论