What Is a Invisible Indexes in Oracle Database?
Maintained during DML but ignored by the optimizer unless invisible-index use is enabled for the session or system.
How the Invisible Indexes Algorithm Works
Maintained during DML but ignored by the optimizer unless invisible-index use is enabled for the session or system.
When to Use Invisible Indexes
Reversible plan tests before dropping an index or assessing a new one.
How to Create a Oracle Database Invisible Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
ALTER INDEX orders_old_ix INVISIBLE;
ALTER INDEX orders_old_ix VISIBLE;Invisible Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT order_id FROM orders WHERE customer_id = :customer_id;Queries Supported by Invisible Indexes
Reversible plan tests before dropping an index or assessing a new one. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Invisible indexes still occupy storage and add write work. Indexes reduce some search work but require storage and add maintenance to writes.
Invisible Indexes Limitations and Common Mistakes
Session optimizer settings can affect the test; visibility does not disable maintenance.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Invisible Indexes
Compare real workload plans and latency in controlled sessions before dropping.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Invisible Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Oracle Database Index Documentation
Check Oracle Database Concepts: Indexes and Index-Organized Tables for release-specific requirements and supported operators.