INDEX VISIBILITY / DISABLE SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL INDEX VISIBILITY / DISABLE Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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: Native since 8.0 PostgreSQL: Not supported SQL Server: Native since 2005 Oracle: Native since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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;