Oracle Virtual Indexes - How to

Virtual index is a feature of Oracle 10 and Oracle 9. The purpose of virtual indexes is
simulate the existence of an index, without actually building it, so you can explain plan as
if the index existed. It is important to drop the index after you're done with it.

To build a virtual index run the following statement:

SQL> CREATE unique INDEX index_name on table_name(col_name) NOSEGMENT /* virtual index */;

To use the index run the following statement:

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

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

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