An invisible index is maintained on write and ignored by the optimiser, which makes dropping an index a reversible experiment instead of a commitment.
ALTER TABLE orders ALTER INDEX idx_legacy INVISIBLE;
-- run the workload. watch for a plan regression.
-- if something breaks, it is one statement back:
ALTER TABLE orders ALTER INDEX idx_legacy VISIBLE;
-- both are instant metadata changes. no rebuild.
SELECT index_name, is_visible FROM information_schema.statistics
WHERE table_name = 'orders';The write cost is still paid while the index is invisible, so this tests whether anything needs it rather than saving anything. A session can still see invisible indexes with SET SESSION optimizer_switch = 'use_invisible_indexes=on', which is how you verify the index would have helped without making it visible to everybody. A week of production traffic is a much better test than a review of the query log.