Oracle Virtual Index

Starting with version 9.2 Oracle introduces the concept of a virtual index, which is a "forged" index whose definition exists only in the data dictionary and has an associated index segment. The virtual index is designed to verify whether the execution of an indexed SQL execution plan is improved if it is not actually created.

This article tests the use of virtual indexes in version 11.2.0.4

1. Create a test table

[Email protected]> CREATE TABLE test_t as SELECT * from Dba_objects; Table created.  [Email protected]> Select COUNT (*) from test_t; COUNT (*)----------86369

2. View a SQL execution plan, access the table using table access full because there is no index created

[email protected]> set autotrace traceonly explain[email protected]>  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, create a virtual index, the data dictionary has the definition of this index but did not actually create this index segment

[Email protected]> set autotrace off[email protected]> CREATE index idx_virtual on test_t (object_id) Nosegment;inde X created. [Email protected]> Select Object_name,object_type from user_objects where object_name= ' idx_virtual '; object_name Object_ TYPE--------------------------------------------------------------------------------------------------------------------- ------------------------------idx_virtual index[email protected]> select Segment_name,tablespace_name from User_ Segments where segment_name= ' idx_virtual '; no rows selected

4. Review the execution plan again

[email protected]> set autotrace traceonly explain[email protected]>  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, we see that the execution plan does not use the index created above, to use the virtual index need to set parameters

[email protected]> alter session set "_use_nosegment_indexes" =true; Session altered.

6. Review the execution plan again, and you can see that the execution plan chooses the virtual index and the time is shortened.

[email protected]> 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)

From the execution plan above, it can be seen that the creation of this index will optimize the effect, this function in the large-scale joint index optimization can do a good job, you can test the combination of multiple columns which is the best combination, without the need to actually create a large index of each combination.

7. Delete Virtual index

[Email protected]> drop index Idx_virtual;index dropped.

MOS document:Virtual Indexes (document ID 1401046.1)

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1890730

Oracle Virtual Index