Did you know we have this? Yes, and this is since 11g.
As you know, the process of tuning SQL statements often requires the testing of alternate indexing strategies to see the effect on execution plans.
Adding extra indexes to large tables can take a considerable amount of time and disk space besides possibly changing known execution plans and possibly affecting all the application behavior.
This can be problematic when you are trying to identify problems on a production system. Some of those problems can be solved by using invisible indexes, but they still would require creation and segment allocation, but also being updated by the application itself.
To solve that, virtual indexes have no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system, similar to invisible indexes, but also don’t not even to be updated, as invisible indexes need to be.
To create the virtual index consists in simply add the NOSEGMENT clause to the CREATE INDEX statement, for example:
CREATE INDEX index_name ON table(column_name) NOSEGMENT;
However, to make the virtual index available we must set the _use_nosegment_indexes parameter. Another important mention is that the virtual indexes don’t appear in the USER_INDEXES view, but can be found in the USER_OBJECTS view.
Cheers!
