Virtual index

From Oracle FAQ
Jump to: navigation, search
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.


[edit] 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;

[edit] 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.

[edit] 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$%' 
SELECT owner, index_name 
  FROM dba_indexes;

[edit] 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;