Invisible index gives you the ability to create an index on production without impacting the queries being fired by application but at the same time test the impact an index creation can cause. Invisible indexes are useful alternative to making an index unusable or to drop it.
The optimizer ignores the index that are marked “Invisible” unless you set the initialization parameter “OPTIMIZE_USE_INVISIBLE_INDEXES” to TRUE. This parameter can be set both at a session level as well as system level.
Usage of Invisible Indexes
One can use invisible index for testing the impact of removing an index. Instead of dropping the index we can make it invisible and its effect.
One can speed up operations by creating invisible indexes for infrequent scenarios. Invisible index will make sure that the overall performance of the application is not affected.
Gives you the flexibility to have both b-tree (to guarantee unique PK) as well as bitmap indexes (on FK columns) in a data warehouse application.
How to create?
Multiple options – either mention “INVISIBLE” clause at the time of index creation or use
ALTER command to make an index “INVISIBLE”.
To make the Index “VISIBLE”
A new column “VISIBILITY” is available in *_INDEXES data dictionary views to know if an index is visible or invisible.
Optimizer didn’t pickup the INDEX into consideration as it’s invisible.
Let’s make it visible in Session level and see the impact