从9.2版本开始Oracle引入了虚拟索引的概念，虚拟索引是一个“伪造”的索引，它的定义只存在数据字典中并有存在相关的索引段。虚拟索引是为了在不真正创建索引的情况下，验证如果使用索引sql执行计划是否改变，执行效率是否能得到提高。
本文在11.2.0.4版本中测试使用虚拟索引
1、创建测试表
|
1
2
3
4
5
6
7
8
9
|
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访问表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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、创建虚拟索引，数据字典中有这个索引的定义但是并没有实际创建这个索引段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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、再次查看执行计划
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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、我们看到执行计划并没有使用上面创建的索引，要使用虚拟索引需要设置参数
|
1
2
3
|
ZX@orcl>
alter
session
set
"_use_nosegment_indexes"
=
true
;
Session altered.
|
6、再次查看执行计划，可以看到执行计划选择了虚拟索引，而且时间也缩短了。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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)
|
从上面的执行计划可以看出创建这个索引会起到优化的效果，这个功能在大表建联合索引优化能起到很好的做作用，可以测试多个列组合哪个组合效果最好，而不需要实际每个组合都创建一个大索引。
7、删除虚拟索引
|
1
2
3
|
ZX@orcl>
drop
index
idx_virtual;
Index
dropped.
|
MOS文档：Virtual Indexes (文档 ID 1401046.1)
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1890730，如需转载请自行联系原作者
![[Oracle]索引](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1634956/20241107/b30f131fc2184af8a7b29e924db9a549.jpeg?x-oss-process=image/resize,h_160,m_lfit)