Using Virtual Indexes

The purpose of virtual indexes is to simulate the existence of an index - without actually building a full index. This allows one to run an explain plan as if the index is present. It is important to drop the index after you're done with it.

To build a virtual index, runs the following statement:

CREATE unique INDEX index_name on table_name(col_name) NOSEGMENT;

To use the index run the following statement:

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

To detect a virtual index in the database run the following sql (these indexes don't have any columns in dba_ind_columns):

SELECT index_owner, index_name  FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%' 
MINUS
SELECT owner, index_name 
FROM dba_indexes;

Oracle has a special hidden "nosegment" clause to the create index syntax that allows you to create an index definition without actually creating the index.

Virtual indexes are used for the statement whose optimizer should be cost. A virtual index is a "fake" index whose definition exists in the data dictionary, but has no index tress association. It is used by Oracle developers to test whether a specific index is going to use useful without having to use the disk space associated with the "real" index.

Screen shots of Virtual index creation using the OEM.

In order to use the virtual index we need to login to the OMS as super user and select a database.

This will lead to an another screen called SQL Analyze

Select the SQL history tab will populate a dialogue box and choose the relevant value from the list.

Choose the SQL statement populated and choose the Virtual index wizard.

You can see a variation in the cost also.

This helps to identify the proper index.