A virtual index is a "fake" index whose definition exists in the data dictionary, but has no associated index segment. The purpose of virtual indexes is to simulate the existence of an index - without actually building a full index. This allows developers to run an explain plan as if the index is present without waiting for the index creation to complete and without using additional disk space.
Creating virtual indexes
The CREATE INDEX statement has a special hidden "nosegment" clause that allows one to create an index definition without actually creating the index.
To build a virtual index, runs the following statement:
CREATE unique INDEX virt_ind_name on table_name(col_name) NOSEGMENT;
Using virtual indexes
To use the index run the following SQL statement:
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Note that virtual indexes are only used when the cost based optimizer is active.
Detecting virtual indexes
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;
Removing virtual indexes
It is important to drop the index after you're done with it. Here is an example:
DROP INDEX virt_ind_name;