Virtual Indexes in Oracle

In the world of virtualization, almost everything is getting virtualized like Virtual Machines, Virtual IP Address, and Virtual reality and so on. There’s one more addition to it “Virtual Indexes”. This is an undocumented feature of Oracle and the virtual indexes are also referred to as “Fake Indexes”.

Virtual Indexes are pseudo-indexes which do not exist as a segment in database. They can be used to test an index usage prior to actually creating one. Virtual indexes allows the CBO to evaluate the index for a SQL statement by building an explain plan that is aware of the new virtual index. This allows a DBA to answer “if an index in created would the optimizer actually use it?” question.

The virtual indexes are very helpful when you have to test against a table with huge data.

Creating a Virtual Index

Create a table using the following statement:

SQL> create table v_test as select object_id rno, object_name name from all_objects;
Table created.

You use the “NOSEGMENT” clause with the CREATE INDEX statement to create a Virtual Index.

SQL> create index v_ind on v_test(rno) NOSEGMENT;
Index created.

Query the USER_SEGMENTS view to verify whether the index segment is created or not.

SQL> select segment_name, bytes from user_segments where segment_name = 'V_IND';
no rows selected
SQL> 
SQL> select segment_name, bytes from user_segments where segment_name = 'V_TEST';
SEGMENT_NAME BYTES
--------------- ----------
V_TEST 196608

As seen above, you have a segment created for the table but none for the index.

Using of Virtual Indexes By setting the hidden parameter “_use_nosegment_indexes“, you inform the optimizer to use any underlying virtual indexes while generating an Explain Plan output.

Note: Hidden parameters should be used after consulting Oracle Support.

SQL> alter session set "_use_nosegment_indexes" = true;

Now, use the Explain Plan command to generate the plan using the following query:

SQL> explain plan for select * from v_test where rno = 5;
Explained.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1019049159
--------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 30 5 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID V_TEST 1 30 5 (0) 00:00:01
* 2 INDEX RANGE SCAN V_IND 17 1 (0) 00:00:01
--------------------------------------------------------------------------------------

It is evident from the above explain plan output that our newly created virtual index can benefit us. It is also clear that the CBO knows about the virtual index existence.

Now, let us trace our SQL and find out whether the virtual index will actually be used by the CBO to fetch the data.

I will use both 10046 and 10053 extended trace events to trace the SQL.

Tracing using 10046

To differentiate my trace file from other existing trace files and to easily identify the trace file, I set tracefile_identifier parameter for my current session, then enable the tracing, run the SQL statement and turn off the tracing as shown below:

SQL> alter session set tracefile_identifier='T10046';
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select * from v_test where rno =5;
SQL> alter session set events '10046 trace name context off';
SQL> exit

I have pasted the section of the trace file in which we are interested i.e., the Execution Plan. Following is the excerpt of the trace file:

=================================================
:
:
WAIT #1: nam='SQL*Net message from client' ela= 2275 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=906714236
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=10769 op='TABLE ACCESS FULL V_TEST (cr=39 pr=0 pw=0 time=69 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=906718527
:
:
=================================================

From the above report it’s clear that CBO is using a “FULL TABLE SCAN” on V_TEST to execute the query. The CBO is intelligent enough to discard the fake index and look for an otherwise optimal plan.

Tracing using 10053

Similarly, I trace the same SQL statement using the 10053 extended trace.

SQL> alter session set tracefile_identifier=’T10053’;
SQL> alter session set events '10053 trace name context forever, level 1';
SQL> alter session set "_use_nosegment_indexes" = true;
SQL> select * from v_test where rno = 5;
SQL> alter session set events '10053 trace name context off';
SQL> exit

It is evident from the trace output shown below, that the CBO is opting for a “FULL TABLE SCAN” as it is aware that V_IND is a virtual index with no segments.

============
Plan Table
============
-------------------------------------+-----------------------------------+
Id Operation Name Rows Bytes Cost Time
-------------------------------------+-----------------------------------+
0 SELECT STATEMENT 11
1 TABLE ACCESS FULL V_TEST 1 21 11 00:00:01
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("RNO"=5)

Conclusion:

As I mentioned earlier, this is undocumented, so use it at your own risk. Virtual indexes should be used when testing queries before creating an index on a huge table as opposed to creating a real index which might take hours depending on the size of the data.

When using the Explain plan, CBO tests the virtual index usage and will let you know whether it will be fruitful to create an index or not. But while actually executing the query it will opt for an optimal execution plan after discarding the virtual index.

Regards