Fung's DBA World

DBA knowledge,standing on the shoulders of giants.

Virtual Index虚拟索引的使用

June 25, 2013

    10g以后,可以在表中创建虚拟索引,以用来测试SQL走索引是否能提高性能。虚拟索引不包含数据,因此不会对数据及存储空间造成影响,基本上作为SQL调优的一种手段。测试如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SH@linora>
SELECT * FROM sh.sales WHERE quantity_sold > 10000; 
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 1550251865 

--------------------------------------------------------------------------------------------- 
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT    |       |     1 |    29 |  4978   (2)| 00:01:00 |       |       | 
|   1 |  PARTITION RANGE ALL|       |     1 |    29 |  4978   (2)| 00:01:00 |     1 |    64 | 
|*  2 |   TABLE ACCESS FULL | SALES |     1 |    29 |  4978   (2)| 00:01:00 |     1 |    64 | 
--------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 

    2 - filter("QUANTITY_SOLD">10000)
    全表扫描,cost4978.
创建虚拟索引:
1
2
3
SH@linora>
ALTER SESSION SET "_use_nosegment_indexes"=TRUE; 
CREATE INDEX sh.sales_vi1 ON sh.sales(quantity_sold) NOSEGMENT;
再次执行sql,查看执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SH@linora>
SELECT * FROM sh.sales WHERE quantity_sold > 10000; 

 Execution Plan 
---------------------------------------------------------- 
Plan hash value: 3712353291 

 ---------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
---------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                   |           |     1 |    29 |     3   (0)| 00:00:01 |       |       | 
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES     |     1 |    29 |     3   (0)| 00:00:01 | ROWID | ROWID | 
|*  2 |   INDEX RANGE SCAN                 | SALES_VI1 |     1 |       |     2   (0)| 00:00:01 |       |       | 
---------------------------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 

    2 - access("QUANTITY_SOLD">10000)
    采用了索引扫描,且cost降为3.
    查看虚拟索引:
1
2
3
4
5
SELECT   index_owner, index_name 
  FROM   dba_ind_columns 
 WHERE   index_name NOT LIKE 'BIN$%' 
MINUS 
SELECT   owner, index_name FROM dba_indexes;
删除方法跟正常索引删除一样:
1
2
3
4
SH@linora>
drop index sales_vi1; 

 Index dropped.

Permalink: http://www.oraclema.com/oracle/virtual-index-simple-use.html