INDEX VISIBILITY / DISABLEIndexes & Search
Buy Me A Coffee

Making an index invisible or disabled so the query optimizer ignores it, without dropping it — useful for testing the impact of index removal.

Temporarily disables constraint checking (DISABLE VALIDATE) or enforcement (DISABLE NOVALIDATE), useful for bulk data loading and migrations. Remember to re-enable (ENABLE) constraints afterward -- never leave them disabled in production.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 INVISIBLE indexes are maintained by the engine on write but ignored by the optimizer on read. Useful for safely testing index removal. Can be toggled with ALTER TABLE t ALTER INDEX idx VISIBLE.
PostgreSQL ✗ Not Supported — No native index visibility toggle. The closest equivalent is enabling/disabling an index via the enable_indexscan / enable_indexonlyscan planner parameters at session scope, or using pg_index.indisvalid. There is no single-index disable command.
SQL Server ✓ Supported 2005 ALTER INDEX idx ON t DISABLE stops the optimizer from using the index and stops maintaining it on writes. REBUILD re-enables it. Note: a disabled clustered index makes the table inaccessible until rebuilt.
Oracle ✓ Supported 8i ALTER INDEX idx UNUSABLE marks it unusable — the optimizer ignores it and DML does not maintain it. ALTER INDEX idx REBUILD restores it. Invisible indexes (optimizer ignores, writes maintained) added in Oracle 11g.
SQLite ✗ Not Supported — No index disable or visibility feature. The only option is to drop the index and recreate it.

Details

Index visibility/disable is a valuable operational tool for testing whether an index is actually being used before committing to dropping it. MySQL's INVISIBLE and Oracle's INVISIBLE (11g+) are the closest equivalents — the index stays current on writes but is hidden from the optimizer, making the test safe and reversible without an index rebuild cost. SQL Server's DISABLE is more destructive: the index is no longer maintained on DML, meaning a REBUILD is required to bring it back (not just a metadata toggle), and disabling a clustered index takes the table offline. PostgreSQL has no single-index disable command — session-level planner knobs or index hints via extensions are the workarounds. For high-traffic tables, MySQL's INVISIBLE approach is the least risky way to validate index removal in production.

Standard Syntax

-- MySQL ALTER TABLE t ALTER INDEX idx INVISIBLE; -- SQL Server ALTER INDEX idx ON t DISABLE;

Version Support

MySQL: Since 8.0 PostgreSQL: Not supported SQL Server: Since 2005 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

INVISIBLE indexes are maintained by the engine on write but ignored by the optimizer on read. Useful for safely testing index removal. Can be toggled with ALTER TABLE t ALTER INDEX idx VISIBLE.

-- Make invisible: ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE; -- Restore: ALTER TABLE orders ALTER INDEX idx_customer VISIBLE; -- Force optimizer to use invisible indexes (session-level): SET SESSION optimizer_switch = 'use_invisible_indexes=on';

SQL Server

ALTER INDEX idx ON t DISABLE stops the optimizer from using the index and stops maintaining it on writes. REBUILD re-enables it. Note: a disabled clustered index makes the table inaccessible until rebuilt.

-- Disable: ALTER INDEX idx_customer ON dbo.orders DISABLE; -- Re-enable: ALTER INDEX idx_customer ON dbo.orders REBUILD;

Oracle

ALTER INDEX idx UNUSABLE marks it unusable — the optimizer ignores it and DML does not maintain it. ALTER INDEX idx REBUILD restores it. Invisible indexes (optimizer ignores, writes maintained) added in Oracle 11g.

-- Unusable (not maintained): ALTER INDEX idx_customer UNUSABLE; -- Invisible (maintained but optimizer ignores): ALTER INDEX idx_customer INVISIBLE; -- Rebuild: ALTER INDEX idx_customer REBUILD;