Invisible indexes oracle 11g

11g allows indexes to be marked as invisible. Invisible indexes are maintained or structured like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.
It can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
A query using the indexes column in the WHERE clause ignores the index and does a full table scan.
Create a table and execute select commands
SET AUTOTRACE ON
SELECT * FROM invisible_table WHERE id = 9999;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INVISIBLE_TABLE | 1 | 3 | 7 (0)| 00:00:01 |
Change the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
SELECT * FROM invisible_table WHERE id = 9999;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |
The index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
ALTER INDEX invisible_table_id VISIBLE;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |
II (Invisible Indexes) can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.
The visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.