Virtual Indexes in Oracle

Virtual Indexes are another undocumented feature used by Oracle. Virtual indexes, as the name suggests are pseudo-indexes that will not behave the same way that normal indexes behave, and are meant for a very specific purpose.A virtual index is created in a slightly different manner than the normal indexes. A virtual index has no segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry for this.

Oracle handles such indexes internally and few required dictionary tables are updated so that the optimizer can be made aware of its presence and generate an execution plan considering such indexes.As per Oracle, this functionality is not intended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack (Virtual Index Wizard).

The virtual index wizard functionality allows the user to test a potential new index prior to actually building the new index in the database.It allows the CBO to evaluate the potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index.

This allows the user to determine if the optimizer would use the index, once implemented.