Invisible Indexes

You can create invisible indexes or make an existing index invisible.

·       An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

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

Creating an Invisible Index

Use the CREATE INDEX statement with the INVISIBLE keyword.

    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)

          INVISIBLE;

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

·       You cannot make an individual index partition invisible. Attempting to do so produces an error.

To make an index invisible:

    ALTER INDEX index INVISIBLE;

To make an invisible index visible again:

   

    ALTER INDEX index VISIBLE;

To determine whether an index is visible or invisible:

    SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES

       WHERE INDEX_NAME = 'IND1';

    INDEX_NAME   VISIBILITY

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

         IND1                     VISIBLE

Understand When to Use Unusable or Invisible Indexes:

·       Use unusable or invisible indexes when you want to improve the performance of bulk loads,

·       Test the effects of removing an index before dropping it,

·       Suspend the use of an index by the optimizer.