Invisible Index In Oracle 11g

Oracle 11g offers a new feature called Invisible Indexes by which an index can be marked as invisible by the Oracle. Once an index is set as invisible, Oracle optimizer no longer considers it. This is a very useful feature where we do not want the index to take effect in a particular table. Lets see how to create a invisible index.
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

Index can be made visible by ALTER INDEX statement as shown below:
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

How to check if an index is visible or invisible

select index_name,visibility from dba_indexes;

INDEX_NAME                             VISIBILITY
————————————– —————————
TAB1_IDX                                 INVISIBLE
TAB2_IDX                                 VISIBLE

An invisible index would never be considered by optimizer except for certain situations which are discussed below:

1. When the paramater OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

2. When hint is given to consider the index as in below example.

select * /*+ index (emp ix_ep_sal) */ ename from emp where sal=1500;

If we want to disable this feature, the index has to be set as visible and OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to FALSE, then indexes will be considered by the optimizer as usual.

ALTER INDEX index_name VISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

Article by Divya