What is an Invisible Index?

An invisible index is an index that exists in the database but is ignored by the query optimizer. Unlike a regular index, which is always considered during query execution, the invisible index is only used when explicitly referenced. This allows for better performance tuning, testing, and maintaining unused or questionable indexes without fully dropping them.

Why use Invisible Indexes?

1. Safe Testing: You can test whether an index is actually being used before permanently removing it.
2. Tuning Queries: If you're tuning queries and unsure if an index is beneficial, make it invisible. This way, you can measure the performance impact without deleting it.
3. Efficient Maintenance: If you suspect an index might be unnecessary or slowing down inserts/updates, making it invisible can help decide whether to drop it without risk.

How to Create an Invisible Index:

In most SQL databases (like Oracle), you can create or alter an index to be invisible with the following command:

ALTER INDEX index_name INVISIBLE;        

To make it visible again, simply use:

ALTER INDEX index_name VISIBLE;        

Practical Use Case:

Imagine you have an index that you suspect is slowing down your INSERT operations. Instead of dropping it and risking query slowdown, you make it invisible. You can monitor the performance impact on the INSERT queries and revert it if needed.

Invisible indexes are a great way to optimize databases without taking irreversible steps, offering flexibility in managing query performance!