Oracle Invisible indexes

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So here is an 11g feature that is actually when known and I really like to use  when considering to remove indexes on clients or even “testing” a creation in production. How does that work?

Invisible indexes

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command. Examples of those commands are listed below:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Cheers!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.