Adding or dropping an index changes the plans of every query on the table, for every user, at once. An invisible index is maintained like any other index but ignored by the optimizer, unless a session asks to use invisible indexes. That lets you test a new index safely, or find out what dropping an existing one would do, without affecting the application.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create index index on table (...) invisible
alter index index {invisible | visible}
alter session set optimizer_use_invisible_indexes = trueHide an Existing Index
The example makes the index on BOOKINGS.BOOKED_AT invisible, explains a query, then lets the session use invisible indexes and explains it again.
Example:
alter index bookings_booked_at_ix invisible; explain plan for select * from bookings where booked_at > timestamp '2026-03-10 00:00:00'; select * from table(dbms_xplan.display(format => 'BASIC')); alter session set optimizer_use_invisible_indexes = true; explain plan for select * from bookings where booked_at > timestamp '2026-03-10 00:00:00'; select * from table(dbms_xplan.display(format => 'BASIC')); alter index bookings_booked_at_ix visible;
Output:
Index BOOKINGS_BOOKED_AT_IX altered. Explained. PLAN_TABLE_OUTPUT _________________________________________ Plan hash value: 2518249199 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| BOOKINGS | -------------------------------------- 8 rows selected. Session altered. Explained. PLAN_TABLE_OUTPUT ________________________________________________________________________ Plan hash value: 2041864784 --------------------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOOKINGS | | 2 | INDEX RANGE SCAN | BOOKINGS_BOOKED_AT_IX | --------------------------------------------------------------------- 9 rows selected. Index BOOKINGS_BOOKED_AT_IX altered.
While invisible, the index is ignored and the query reads the whole table. With OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE in this session only, the same query uses the index. Finally the index is made visible again.
Create an Index Invisible
A new index can be created invisible, tested in one session, and made visible when it proves useful.
Example:
-- create an index invisible from the start, to test it before others see it
create index bookings_status_ix_test on bookings (status) invisible;
select index_name, visibility, status
from user_indexes
where index_name in ('BOOKINGS_STATUS_IX_TEST', 'BOOKINGS_BOOKED_AT_IX');Output:
Index BOOKINGS_STATUS_IX_TEST created. INDEX_NAME VISIBILITY STATUS __________________________ _____________ _________ BOOKINGS_STATUS_IX_TEST INVISIBLE VALID
USER_INDEXES shows VISIBILITY = INVISIBLE, while STATUS = VALID: the index is maintained on every change. The example drops it afterward.
Things to Know
- Before dropping an index, make it invisible for a while; if performance suffers, make it visible again instantly instead of rebuilding it.
- An invisible index still costs maintenance on every insert, update, and delete, and still enforces uniqueness if it is unique.
- The parameter can also be set system-wide, but per session is the point of testing.
Related Guides
Conclusion
Invisible indexes are maintained but ignored by the optimizer except in sessions that enable OPTIMIZER_USE_INVISIBLE_INDEXES. Use them to test new indexes and to rehearse dropping old ones without changing plans for everyone.
