How Is a Database Space Saved Using a Virtual Index?

The virtual index feature allows you to turn off physical storage for specified indexes.

Note: Virtual indexes are intended to be used in situations where the maximum number of indexes supported by dbdef has been exceeded. While virtual indexes conserve physical storage, they are built at run-time, thus they must be built every time they are used by a form or report to access data. This generally has a negative impact on system performance.

A virtual index should not be created for any data set that is accessed frequently. If you need to add an index to a table, and need to use a virtual index, review all the indexes for that table in order to select the index that will impact your system performance the least.

One scenario that supports use of a virtual index is when you are certain an index will not be used in your system. For example, if index ABCSET was designed for use between two applications and you do not have the second application installed, index ABCSET could be set to virtual.

Application developers or database designers create virtual indexes using the Database Definition (dbdef) utility.

When Can I Create a Virtual Index?

  • A unique index can also be a virtual index only if there is another non-virtual index that enforces the uniqueness constraint. In other words, the virtual index must be contained within another unique index.

  • A conditional index cannot be a virtual index.

  • A primary index cannot be deployed as a virtual index.

How Are Virtual Indexes Handled by the Database Interface?

  • The blddbdict utility and the database driver (oradb11 / oradb12) both process the Virtual flag.

    Note: Since performance may be impacted when processing without indexes in place, comments are generated with SQL code so the database administrator can easily determine whether a query should be using an index. This applies only to SQL databases that support hints; if index hints are turned off, no comments are generated.

  • The verifyora11 / verifyora12 utility displays an informational message when a virtual index occurs in a table definition.

  • The bldora11ddl / bldora12ddl utility does not create DDL for virtual indexes.