Oracle Invisible Index 

An Invisible Index is a normal Oracle index that still exists and is fully maintained by the database, but is ignored by the optimizer by default when Oracle chooses an execution plan.

The easiest way to remember it:

The index is still there and still maintained — the optimizer simply doesn't normally consider it.

This feature is especially useful when you want to test an existing index before dropping it or test a new index before exposing it to normal application queries.

1. How Does It Work?

A normal index is VISIBLE:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Oracle can consider this index for a query such as:

SELECT *
FROM orders
WHERE customer_id = 100;

You can make the index invisible:

ALTER INDEX idx_orders_customer INVISIBLE;

Now the index still exists, but the optimizer normally excludes it when choosing an execution plan.

VISIBLE INDEX

Query
  ↓
Optimizer
  ↓
Index can be considered


INVISIBLE INDEX

Query
  ↓
Optimizer
  ↓
Index normally ignored

You can return it to normal visibility at any time:

ALTER INDEX idx_orders_customer VISIBLE;

No index rebuild is required.

2. Invisible Does Not Mean Disabled

This is the most important point.

An invisible index is still maintained during:

INSERT
UPDATE
DELETE

For example, if an indexed value changes, Oracle also updates the corresponding invisible index entry.

Therefore, an invisible index still:

• consumes storage;
• requires DML maintenance;
• generates redo when its entries are modified;
• requires CPU and I/O for maintenance.

So making an index invisible does not remove its DML overhead.

If you want to completely remove that overhead, the index eventually needs to be dropped.

3. Why Is It Useful?

Suppose you have an old production index and aren't sure whether it is still needed.

You could immediately:

DROP INDEX idx_orders_customer;

But if an important query depends on it, you may have to recreate the entire index.

A safer approach is:

ALTER INDEX idx_orders_customer INVISIBLE;

Then monitor the workload.

If performance becomes worse:

ALTER INDEX idx_orders_customer VISIBLE;

The index is immediately available to the optimizer again without rebuilding it.

This gives you a useful workflow:

VISIBLE
   ↓
INVISIBLE
   ↓
Monitor
   ↓
   ├── Problem → VISIBLE
   │
   └── No problem → Consider DROP

4. Testing a New Index

Invisible indexes are also useful when introducing a new index.

You can create it as invisible:

CREATE INDEX idx_trans_customer_date
ON transactions(customer_id, transaction_date)
INVISIBLE;

Normal sessions won't normally consider it.

In your testing session:

ALTER SESSION
SET optimizer_use_invisible_indexes = TRUE;

Now the optimizer in your session is allowed to consider invisible indexes.

This allows you to test the new index without immediately making it available to normal application sessions.

One important detail:

OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE does not force Oracle to use the index.

It only allows the Cost-Based Optimizer to consider it.

5. Invisible vs Unusable

These are completely different concepts.

Invisible

The index:

• exists;
• is valid;
• is maintained;
• can be used when invisible indexes are enabled;
• is normally ignored by the optimizer.

Unusable

An unusable index is not available for normal index access and generally needs to be rebuilt:

ALTER INDEX idx_orders_customer REBUILD;

So:

INVISIBLE
    =
Valid index hidden from normal
optimizer consideration


UNUSABLE
    =
Index unavailable for normal access
and generally requires rebuild

6. Invisible vs Dropped

This is why invisibility is a useful intermediate state before dropping an index.

7. Checking Invisible Indexes

For your schema:

SELECT index_name,
       table_name,
       visibility,
       status
FROM user_indexes
WHERE visibility = 'INVISIBLE';

You may see:

INDEX_NAME            VISIBILITY   STATUS
--------------------  -----------  ------
IDX_ORDERS_CUSTOMER   INVISIBLE    VALID

Notice:

VISIBILITY = INVISIBLE
STATUS     = VALID

This perfectly describes the feature: the index is valid, but normally hidden from the optimizer.

8. Main Use Cases

Invisible indexes are useful for:

Testing old indexes before dropping them

VISIBLE → INVISIBLE → MONITOR → DROP

Testing new indexes

CREATE INVISIBLE
       ↓
Test in your session
       ↓
Make VISIBLE

Index cleanup

Safely evaluate potentially redundant indexes before permanently removing them.

Performance troubleshooting

Observe how execution plans behave when a particular index is removed from normal optimizer consideration.

Key Point

The main concept is simple:

Oracle Invisible Index = a real, valid and maintained index that the optimizer normally ignores.

It gives you a safer way to manage indexes:

             Invisible Index
                    |
        +-----------+-----------+
        |                       |
        v                       v
   DML Maintenance          Optimizer
        |                       |
      ACTIVE               IGNORED
                            by default

That makes invisible indexes particularly valuable when testing new indexes or evaluating existing indexes before dropping them.