Invisible Index

Invisible index is an index can be ignored by the optimizer unless you set the OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE at the session or system level.  The default value is FALSE.  Invisible index is maintained during DML statements.

Create an index as invisible

SQL > create index APP_OWNER.TRANX (IDN) invisible;


Index is alter as not visible to optimizer:

SQL> alter index APP_OWNER.TR_INDX9 invisible;
Index altered.


The optimizer will always consider the index:

SQL> alter index APP_OWNER.TR_IDX9 visible;