Invisible Index

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

1) Test the removal of an index before dropping it.
2) Use temporary index structures for certain operations or modules of an application without
affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.

To create an invisible index, use the SQL statement CREATE INDEX with the INVISIBLE clause.
The following statement creates an invisible index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename)
      TABLESPACE users
      STORAGE (INITIAL 20K
      NEXT 20k
      PCTINCREASE 75)
      INVISIBLE;

Making an Index Invisible

To make a visible index invisible, issue this statement:

ALTER INDEX index INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX index VISIBLE;

To find out whether an index is visible or invisible, query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. For example, to determine if the index IND1 is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY
FROM USER_INDEXES
WHERE INDEX_NAME = 'IND1';

INDEX_NAME     VISIBILITY
------------------    -------------
IND1                  VISIBLE