Invisible Indexes in Oracle Database 11g

Invisible Indexes in Oracle Database 11g

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained 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. Indexes 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;

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES