Oracle性能调优之虚拟索引用法简介详解数据库

本博客记录一下Oracle虚拟索引的用法，虚拟索引是定义在数据字典中的伪索引，可以说是伪列，没有修改的索引字段的。虚拟索引的目的模拟索引，不会增加存储空间的使用，有了虚拟索引，开发者使用执行计划的时候也不需要等索引完全创建好才可以看到效果

ok，这里找张用户表来测试一下虚拟索引

//设置执行计划 
SQL> set autotrace traceonly 
 
//查询用户，因为没加索引，所以是全表扫描 
SQL> select * from sys_user where username='admin'; 
 
//创建虚拟索引，记得加关键字nosegment 
SQL>create index idx_username on sys_user(username) nosegment; 
 
//先关了执行计划自动打印 
SQL>set autotrace off 
 
//查一下索引表里有对应索引字段？这里没查到，说明虚拟索引并没有创建索引列 
SQL>  select index_name from dba_indexes where table_name='sys_user' and index_n 
ame='IDX_USERNAME'; 
未选定行 
 
//再查一下索引对象里有数据？这里可以查到，说明虚拟索引还是有创建索引对象的 
SQL> select object_name,object_type from dba_objects where object_name='IDX_USER 
NAME'; 
OBJECT_NAME          OBJECT_TYPE 
--------------------              ------------------- 
IDX_USERNAME           INDEX 
 
//再开启执行计划自动打印 
SQL> set autotrace traceonly 
 
//查询一下，发现还是TABLE ACCESS FULL，并没有走索引，不是创建了虚拟索引了？ 
SQL> select * from sys_user where username='admin'; 
执行计划 
---------------------------------------------------------- 
Plan hash value: 4234589240 
------------------------------------------------------------------------------ 
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT  |          |     1 |   272 |     3   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| SYS_USER |     1 |   272 |     3   (0)| 00:00:01 | 
------------------------------------------------------------------------------ 
...//省略执行计划信息 
 
//注意：虚拟索引使用，需要设置一下，改为true才可以 
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true; 
会话已更改。 
 
//再次查询，可以看到走虚拟索引了，TABLE ACCESS BY INDEX ROWID，虚拟索引使用成功 
SQL> select * from sys_user where username='admin'; 
执行计划 
---------------------------------------------------------- 
Plan hash value: 1796849462 
-------------------------------------------------------------------------------- 
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) 
| Time     | 
-------------------------------------------------------------------------------- 
------------ 
|   0 | SELECT STATEMENT            |              |     1 |   272 |     2   (0) 
| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| SYS_USER     |     1 |   272 |     2   (0) 
| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN          | IDX_USERNAME |     1 |       |     1   (0) 
| 00:00:01 | 
...//省略执行计划信息 

综上分析，虚拟索引创建关键的步骤是：

alter session set "_use_nosegment_indexes"=true; 
 
create index index_name on table_name(col_name) nosegment;

虚拟索引也可以删除，用法和删索引一样：

drop index [索引名称] on [表格名称]

这里就是虚拟索引的简要介绍，假如在生产环境，怕加了索引影响系统性能，或许可以加虚拟索引，怕占太多存储空间，也可以加上虚拟索引，虚拟索引有时候常被用于SQL调优，虚拟索引关键字是nosegment