Invisible Index Oracle 11.1 and above.

Starting in Oracle 11.1 is it possible to test index before starting to use it with all sessions. And it is also possible to test affect of index dropping before actually dropping the index.  These can be done with invisible index feature which allows you to set optimizer seeing index only in the session you are using.

Here is a couple of examples how to do this:

Create invisible index:


SQL> CREATE INDEX test_invis_idx ON test_table (column_name) INVISIBLE;

There is new init param 'optimizer_use_invisible_indexes' which tells if optimizer sees the invisible indexes. And default values for this parameter is false ( so optimizer does not see invisible indexes by default and because of this queries does not use this kind of indexes). You can set this parameter true for the session you are using and test new index with only that session:
SQL> ALTER SESSION SET optimizer_use_invisible_indexes=true;

Then run queries that are using new index and if everything works like you want then change index visible and all sessions start to use new index because optimizer sees this index ( all new indexes are visible by default ):

SQL> ALTER INDEX test_invis_idx  VISIBLE;

And same way you can test affect of dropping index. First change index you like to drop invisible and see if queries are starting to working poorly. If they are change index back to visible. And if they are working ok without the index you can drop it safely.

SQL> ALTER INDEX test_invis_idx  INVISIBLE;
SQL> DROP INDEX test_invis_idx;


You can check index state from the user_indexes , all_indexes or dba_indexes views like this:

SQL> select index_name,VISIBILITY from user_indexes where index_name='test_invis_idx';

INDEX_NAME                     VISIBILIT

------------------------------ ---------

TEST_INVIS_IDX           VISIBLE