While adding some indexes to production table to see some performance improvements; Virtual Index creation is very handy. It doesn’t occupy spaces and you could compare query plan to see if that going to help query performance. Here is a demonstration about how that can be achieved.
Create a sample table where we’ll test Virtual Index impact.
If we query the table using the primary key, we can see this reflected in the execution plan.
Now If we query the table using a non-indexed column, we see a full table scan.
To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.
If we repeat the previous query we can see the virtual index is not visible to the optimizer.
To make the virtual index available we must set the _use_nosegment_indexes parameter.
If we repeat the query we can see that the virtual index is now used.
Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.