- It can be used for disabling or making invisible a problematic (large in size) index causing performance issues. You can make it visible once after the issue has been addressed properly.
- If you wanted to test some newly introduced index without affecting the other operations. The new index can put in invisible mode which will not affect the other normal operations with the table and complete the testing. Once the testing is completed and it is giving x times better performance the index can be make it to visible mode.
- Invisible indexes are very useful during some certain performance issues. Suppose if you don’t wanted to use a large index during performance issue, prior to 11g we need to drop the index or make it unusable and test the scenario. But this 11g feature simplifies this method without dropping the index. Index rebuild is needed to make an unusable index to usable and it consumes time.
- Invisible indexes can be used in only for specific applications without changing the execution plans for the other modules as it can be enabled or make it visible for session specific.
- Another potential advantage of invisible index is the situations where we need an index as temporary purpose. An invisible index can be created temporarily for specific SQL statements without affecting the rest of the database.
- Invisible indexes are alternative for dropping an index just for testing some performance issue scenarios.