Virtual Indexes

Recently I have been to one of our clients for performance tuning of few processes. The process of tuning SQL queries requires testing of different alternatives of indexes. It becomes difficult in production environment where lots of people are connected to the database and tables are large.

Virtual indexes helps in such situations. Since virtual indexes does not use any segment, time required to create index and disk space required is irrelevant.

Command to create virtual index:

CREATE INDEX IDX_TAB ON TAB1(COL1,COL2) NOSEGMENT;

To make virtual index available, parameter _use_nosegment_indexes needs to be set to true.

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

The USER_INDEXES view does not keep information about virtual indexes. Only USER_OBJECTS view keeps the record.