Testing Index Impact Safely with Oracle Invisible Indexes

The problem: you need to know if dropping an index will hurt performance

Before removing an index you often want to see whether the optimizer will choose a different access path. Dropping the index, running a workload, and then recreating it is disruptive and time‑consuming, especially in production. Oracle provides a lighter‑weight alternative: make the index invisible so the optimizer ignores it while the index remains maintained by DML.

What an invisible index does

An invisible index is still a physical segment; INSERT, UPDATE, and DELETE statements continue to update it, and it occupies the same storage as a visible index. The only difference is that, by default, the cost‑based optimizer does not consider it when generating execution plans. You can toggle visibility with DDL:

ALTER INDEX schema_name.index_name INVISIBLE;
ALTER INDEX schema_name.index_name VISIBLE;

The change is immediate, but it is DDL, so it takes an exclusive lock on the index and can invalidate dependent cursors. Schedule it outside peak write windows.

Worked example: rehearsing a drop

1. Prepare the index

Assume you have a table SALES and an index IDX_SALES_DATE on the SALE_DATE column. First gather statistics so the optimizer has accurate information:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'SALES');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SALES', 'IDX_SALES_DATE');

2. Make the index invisible

Run the ALTER statement as the index owner or a user with ALTER ANY INDEX privilege:

ALTER INDEX SALES.IDX_SALES_DATE INVISIBLE;

3. Capture a baseline plan

Run the representative query (or workload) and fetch its plan:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM SALES
WHERE SALE_DATE BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

Note whether the plan shows an index range scan on IDX_SALES_DATE. Because the index is invisible, the optimizer should pick a different path (e.g., a full table scan or another index).

4. Verify the index is still maintained

To confirm the index is still being updated, you can measure DML time before and after making it invisible (or simply check USER_INDEXES for the segment size). The index will not disappear from the data dictionary:

SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE INDEX_NAME = 'IDX_SALES_DATE';
-- returns VISIBILITY = INVISIBLE

5. Restore visibility if needed

If the plan regression is unacceptable, make the index visible again:

ALTER INDEX SALES.IDX_SALES_DATE VISIBLE;

Repeat the query and verify that the index range scan returns.

Trade‑off and limitation

The main benefit is reversibility: you can test the effect of dropping an index without actually removing it, avoiding downtime and the need to rebuild the segment later. The limitation is that invisible indexes still consume storage and incur write overhead. If your goal is to reduce I/O or reclaim space, invisibility does not help; you must drop or rebuild the index. Additionally, because the ALTER is DDL, it can cause a brief pause and invalidate cursors, so avoid running it during high‑transaction periods.

Actionable closing

Use invisible indexes as a rehearsal step in your change‑management workflow:

1. Create or ensure the target index exists and has fresh statistics.
2. Make it invisible.
3. Run the workload and compare plans with DBMS_XPLAN.DISPLAY_CURSOR.
4. Decide whether to keep, modify, or drop the index based on the observed impact.
5. Restore visibility or drop the index as appropriate.

Remember to check USER_INDEXES.VISIBILITY to confirm the state change, and always test with production‑like statistics and data volume to avoid misleading results.