Oracle虚拟索引

197次阅读

共计 3507 个字符，预计需要花费 9 分钟才能阅读完成。

从 9.2 版本开始 Oracle 引入了虚拟索引的概念，虚拟索引是一个“伪造”的索引，它的定义只存在数据字典中并有存在相关的索引段。虚拟索引是为了在不真正创建索引的情况下，验证如果使用索引 sql 执行计划是否改变，执行效率是否能得到提高。
本文在 11.2.0.4 版本中测试使用虚拟索引

1、创建测试表
ZX@orcl> create table test_t as select * from dba_objects;

Table created.

ZX@orcl> select count(*) from test_t;

COUNT(*)
———-
86369

2、查看一个 SQL 的执行计划，由于没有创建索引，使用 TABLE ACCESS FULL 访问表

ZX@orcl> set autotrace traceonly explain
ZX@orcl> select object_name from test_t where object_id=123;

Execution Plan
———————————————————-
Plan hash value: 2946757696

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 1106 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST_T | 14 | 1106 | 344 (1)| 00:00:05 |
—————————————————————————-

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

1 – filter(“OBJECT_ID”=123)

Note
—–
– dynamic sampling used for this statement (level=2)

3、创建虚拟索引，数据字典中有这个索引的定义但是并没有实际创建这个索引段
ZX@orcl> set autotrace off
ZX@orcl> create index idx_virtual on test_t (object_id) nosegment;

Index created.

ZX@orcl> select object_name,object_type from user_objects where object_name=’IDX_VIRTUAL’;

OBJECT_NAME OBJECT_TYPE
——————————————————————————————————————————– ——————-
IDX_VIRTUAL INDEX

ZX@orcl> select segment_name,tablespace_name from user_segments where segment_name=’IDX_VIRTUAL’;

no rows selected

4、再次查看执行计划
ZX@orcl> set autotrace traceonly explain
ZX@orcl> select object_name from test_t where object_id=123;

Execution Plan
———————————————————-
Plan hash value: 2946757696

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 1106 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST_T | 14 | 1106 | 344 (1)| 00:00:05 |
—————————————————————————-

5、我们看到执行计划并没有使用上面创建的索引，要使用虚拟索引需要设置参数
ZX@orcl> alter session set “_use_nosegment_indexes”=true;

Session altered.

6、再次查看执行计划，可以看到执行计划选择了虚拟索引，而且时间也缩短了。
ZX@orcl> select object_name from test_t where object_id=123;

Execution Plan
———————————————————-
Plan hash value: 1533029720

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 1106 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_T | 14 | 1106 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_VIRTUAL | 315 | | 1 (0)| 00:00:01 |
——————————————————————————————-

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

2 – access(“OBJECT_ID”=123)

Note
—–
– dynamic sampling used for this statement (level=2)