oracle性能调优-虚拟索引

      在数据库表数据比较大的时候，我们自然会想到利用index优化来提高性能，当是否真的需要index需要我们考虑。用或者不用，我们可以先通过虚拟索引先来测试一下sql的性能。使用虚拟索引不用故意的去避开业务的高峰期，担心大表创建索引对系统性能产生影响。

      虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。作用仅仅是为了DBA作SQL优化时使用，DBA根据虚拟索引的优化效果决定是否创建物理索引。

一、虚拟索引的类型

B-TREE索引

BIT位图索引

在CBO模式下ORACLE优化器会考虑虚拟索引，但是在RBO模式下需要添加hint才行。

二、创建虚拟索引

a、创建一张测试表：

create table test as select * from dba_objects;

b、 创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true:

alter session set "_use_nosegment_indexes"=true;

c、虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字：

create index ix_test_id on t(object_id) nosegment;

三、虚拟索引使用

SQl：

select * from t where object_id=1

a、没有使用索引的执行计划

SQL> explain plan for select * from test where object_id=1;
Explained
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   289   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   289   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected

b、使用虚拟索引的执行计划

SQL> explain plan for select * from test where object_id=1;
Explained
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2444949661
--------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    12 |  2484 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |    12 |  2484 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | IX_TEST_ID |   258 |       |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
18 rows selected

从对比可以看出使用虚拟索引可以用来帮助我们优化sql。

四、一些需要注意的地方

a、虚拟索引无法执行alter index选项

b、虚拟索引分析并且有效，但是数据字典里查不到结果。

c、使用回收站特性的时候，虚拟索引必须显示drop，才能创建同名的索引。

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

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

参考文档：

http://blog.itpub.net/26015009/viewspace-2080872/

http://www.cnblogs.com/Richardzhu/p/3487594.html