Few days back I wrote about virtual indexes which help us in checking the impact of indexes on SQL query execution plan without actually creating the index thus saving us time and most important I/O from and to Oracle resources (Disk, CPU, etc).

Now let suppose the situation is totally opposite of what we encounter above, the index already exist on the table, but we have a doubt that it might be having a negative impact on few queries. To confirm or test it out we had to either drop the index or make it UNUSABLE.

Both choices have a side effect:

If we drop it and then later we have to recreate it after testing has been done, this will result in unnecessary wastage of time and oracle resources.

If we make it UNUSABLE , it will result in all DML failing which is totally un expectable in 99% of the situation.

Now with Oracle 11g, we have a choice to make index invisible.

With simple ALTER INDEX statement as follows:

ALTER INDEX emp_idx INVISIBLE;

Once the index is invisible it will not be used in any query unless explicitly mention as hint in the query, but will not have any impact on DML operations i.e DML operation will continue to update and delete from index as usual.

Technically it will only become invisible to optimizer unless SQL statement explicitly specify the index with a hint.

Once we have done with our testing we can make the index visible again through following ALTER INDEX statement

ALTER INDEX emp_idx VISIBLE;
OR
If we rebuilt the index it will be visible again.