Oracle 11g New Feature - Invisible Indexes

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.

Using invisible indexes, you can do the following:
· Test the removal of an index before dropping it.
· Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Making an index invisible is an alternative to making it unusable or dropping it.

Unlike Unusable Index, an invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint.

Applications often have to be modified without being able to bring the complete application offline. Invisible indexes enable you to leverage temporary index structures for certain operations or modules of an application without affecting the overall application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.

You can set following Oracle Parameter to make the Invisible Indexes visible to Optimizer or not.

OPTIMIZER_USE_INVISIBLE_INDEXES enables or disables the use of invisible indexes.
Values:
· true - Invisible indexes are treated as visible (normal) indexes.
· false - Invisible indexes will not be considered by the optimizer but will still be maintained by DML operations.

Creating an Invisible Index

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