Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). So basically the index will be visible to optimizer only in our sessions. Optimizer running query in other sessions won’t be able to see this index.

Virtual Index in Oracle

Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter “_use_nosegment_indexes” is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.

Utility of using virtual index is that, suppose we have a table having huge number of rows and it is getting joined with other tables. If we see that optimizer is creating a plan which is costly and SQL tuning advisor suggest us to create an index on a column, then in case of production database we cannot simply create an index and test the changes. We need to make sure that creating that index wont have any negative impact on the execution plan of other queries running in this database.

So there is where we can use virtual index. Here is how virtual index works.

1) Creating a table

2) Try selecting a value from test table

3) Create a virtual Index on test table

In order to create a virtual index, we need to give NOSEGMENT at the end of the create index statement.

This will just create a index on the object_name column of test table. But it will not create an index segment in database.
You can check this by querying dba_objects and dba_indexes tables.

So, object exists in database, but we don’t have segment for the same.

Now if you try to run the same select command on test table, still optimizer will NOT use virtual index.

In order for optimizer to use virtual index, you need to set a parameter called _USE_NOSEGMENT_INDEXES in your session

Once you set this hidden parameter, optimizer will start using the virtual index you created on this table

If you run this query from some other session, it wont use virtual index. you can analyze virtual indexes

You CANNOT rebuild a virtual index

Finally, if the index doesn’t seem to satisfy your requirement, then you can drop the virtual index.

In the next article, we show how to use invisible index.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn:

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.