Virtual index

Sometimes you want to test if an index Works or not.

You sometimes want to test even in production but of course

There is always a risk when you want to test something in production

The solution is simple : Use virtual index as of oracle 10G

SQL> create index test on table test1

          ( ID)

           NOSEGMENT

           COMPUTE STATISTICS;

Index is created but if you check the explain plan the index is still not used

Well first you got to do this.

SQL> alter session set “_use_nosegment_indexes”=true;

The virtual index is now used only in you session. Other users can’t

Use the index so the don’t have the advantage or the disadvantage

When you finished testing you can drop the index.