Virtual index

OEM's Virtual Index Wizard

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;