Oracle virtual index

Since version 9.2, Oracle introduced the concept of virtual index, which is a "forged" index whose definition only exists in the data dictionary and has related index segments. The purpose of the virtual index is to verify whether the execution efficiency can be improved if the execution plan changes using the index sql without actually creating the index.

This article tests the use of virtual indexes in version 11.2.0.4.

1. Create a test table

ZX@orcl > create table test_t as select * from dba_objects;Table created.ZX@orcl > select count (*) from test_t; COUNT (*)-86369

2. Check the execution plan of a SQL. Since no index is created, use TABLE ACCESS FULL to access the table

ZX@orcl > set autotrace traceonly explainZX@orcl > select object_name from test_t where object_id=123 Execution Plan---Plan hash value: 2946757696 Murray- -| 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. Create a virtual index. The index is defined in the data dictionary but the index segment is not actually created

ZX@orcl > set autotrace offZX@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 INDEXZX@orcl > select segment_name Tablespace_name from user_segments where segment_name='IDX_VIRTUAL' No rows selected

4. View the execution plan again

ZX@orcl > set autotrace traceonly explainZX@orcl > select object_name from test_t where object_id=123 Execution Plan---Plan hash value: 2946757696 Murray- -| 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. We see that the execution plan does not use the index created above. To use the virtual index, you need to set parameters.

ZX@orcl > alter session set "_ use_nosegment_indexes" = true;Session altered.

6. If you look at the execution plan again, you can see that the execution plan has selected a virtual index, and the time has been shortened.

ZX@orcl > select object_name from test_t where object_id=123 Execution Plan---Plan hash value: 1533 029720 Murray- -| 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)

From the above execution plan, we can see that creating this index will have the effect of optimization. This function can play a good role in large table joint index optimization, and it can test which combination of multiple columns works best. There is no need to actually create a large index for each combination.

7. Delete virtual index

ZX@orcl > drop index idx_virtual;Index dropped.

MOS document: Virtual Indexes (document ID 1401046.1)