Making an Index Invisible

郑全

Making an 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. 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:

• Submit the following SQL statement:

  ALTER INDEX index INVISIBLE;
  

To make an invisible index visible again:

• Submit the following SQL statement:

  ALTER INDEX index VISIBLE;
  

To determine 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