11g New Feature : Invisible Index

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer and the user unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. The default value for this parameter is FALSE.

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;


To make a visible index invisible, issue this statement:

ALTER INDEX index_name INVISIBLE;


To make an invisible index visible, issue this statement:

ALTER INDEX index_name VISIBLE;



To find out whether an index is visible or invisible, query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. Column VISIBILITY is newly added to these views.