一、背景
在生产环境上,经常会出现一些生产环境有而测试环境没有的性能问题,而这些性能问题可能是由于某条语句性能差导致的,索引通常是优化SQL的很好手段,但是在生产环境任何的DDL操作都应该谨慎,创建没加online的索引时,会对该对象的DML操作产生阻塞。这是我们假象,如果这张表有这个索引,那又会生成什么样的执行计划,这就是虚拟索引的用处。
适用:OACLE 11G及以后的版本
二、虚拟索引概念
虚拟索引指没有创建对应的物理实体的索引,虚拟索引的目的,是在不耗时、CPU、IO、大量存储空间去实际创建索引的情况下,来判断一个索引是否能够对SQL优化起到作用。nosegment选项表明索引是虚拟的,_use_nosegment_indexes指示数据库可以在执行计划中使用虚拟索引。
三、示例
SQL> create table temp_123 as select * from user_objects;
表已创建。
SQL> explain plan for select * from temp_123 where object_id=1630654;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3542478759
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1600 | 315 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEMP_123 | 10 | 1600 | 315 (1)| 00:00:05 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=1630654)
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement
已选择18行。
SQL> alter session set "_use_nosegment_indexes"=true;
会话已更改。
SQL> create index idx_temp on temp_123(object_id) nosegment;
索引已创建。
SQL> explain plan for select * from temp_123 where object_id=1630654;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4276853690
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1600 | 5 (0)| 00
:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP_123 | 10 | 1600 | 5 (0)| 00
:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEMP | 626 | | 1 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1630654)
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement
已选择19行。
SQL> SELECT * FROM USER_INDEXES WHERE INDEX_NAME='IDX_TEMP';
未选定行