When tuning database performance, removing unused indexes can help reduce write overhead and improve efficiency. But dropping an index outright is risky, especially in production systems, because it’s hard to know for sure whether it’s still needed. Oracle Database offers a practical solution: invisible indexes. This feature allows you to hide an index from the optimizer without deleting it, giving you a safe way to test and fine-tune your indexing strategy.
An invisible index behaves like a regular index in most respects. It is maintained during inserts, updates, and deletes. It consumes storage and has a presence in the data dictionary. However, it is ignored by the optimizer when generating execution plans for SQL statements unless explicitly instructed to consider it.
Creating an Invisible Index
To define an index as invisible at creation time, the INVISIBLE keyword is used:
CREATE INDEX idx_salary ON employees(salary) INVISIBLE;
In this example, the index on the salary column will be maintained, but Oracle’s optimizer will not consider it when evaluating execution plans.
Making an Existing Index Invisible
You can also alter an existing index to become invisible:
ALTER INDEX idx_salary INVISIBLE;
To revert the change and make the index visible again:
ALTER INDEX idx_salary VISIBLE;
This change is instantaneous and does not require the index to be rebuilt. It is also fully reversible.
Verifying Index Visibility
To check the visibility status of an index, query the DBA_INDEXES or USER_INDEXES data dictionary view:
SELECT index_name, visibility
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
This will show whether each index is VISIBLE or INVISIBLE.
Forcing the Optimizer to Use Invisible Indexes
By default, the optimizer does not use invisible indexes. However, you can enable their use in a specific session by setting the following parameter:
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
With this setting in place, the optimizer will consider invisible indexes as if they were visible. This is particularly useful when testing query performance with and without a specific index.
Alternatively, you can use a SQL hint to explicitly direct the optimizer to use a specific index, even if it is invisible:
SELECT /*+ INDEX(employees idx_salary) */ *
FROM employees
WHERE salary > 100000;
This gives fine-grained control over execution plans without changing global settings or making the index permanently visible.
Use Cases for Invisible Indexes
Invisible indexes are helpful in scenarios where performance needs to be tested under different indexing strategies. For example, if you suspect that an index is unused or causing performance issues, you can make it invisible and observe how queries behave without it. This avoids the risk of dropping an index that might still be needed.
Invisible indexes also provide a safe way to prepare for index removal in production systems. If no queries rely on the index while it is invisible, it is likely safe to drop it later.
They can also be used for temporarily disabling indexes during bulk data loads, without affecting the application logic that relies on the schema.
Schneide Blog 