What are Invisible Indexes?
Beginning with Oracle Database 11g Release 1, 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. Although you can make a partitioned index invisible, you cannot make an individual index partition invisible while leaving the other partitions visible. 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.
How to use the Invisible Indexes at the Session level
SQL> show parameter optimizer_use_invisible_indexes; NAME TYPE VALUE ---- ---- ----- optimizer_use_invisible_indexes boolean FALSE SQL> alter session set optimizer_use_invisible_indexes=TRUE; Session altered. SQL> show parameter optimizer_use_invisible_indexes; NAME TYPE VALUE ---- ---- ----- optimizer_use_invisible_indexes boolean TRUE
How to make invisible index visible
You can just use the alter index command ALTER INDEX index_name VISIBLE;
How to make visible index invisible
ALTER INDEX index_name INVISIBLE;