Invisible Indexes in Oracle

How can you test the effect of not using an index without actually dropping it? An invisible index is an alternative to making an index unusable. When you make an index invisible, you actually hide it from the optimizer so that it is not used by any queries hitting the underlying table. Behind the scene, invisible indexes are real, maintained by DML but cannot be used by the optimizer. Good way to test performance of queries without dropping the table right away. Alternatively, you could drop an index and do the same test. For large tables, dropping and creating indexes can be a time consuming overhead all of which can be avoided with invisible indexes

Lets us see how it works

Step1

Create table from all_objects

create table test_all_objects as select * from all_objects

Step2

Create an index on column object_name

CREATE INDEX IDX_OBJ_NAME_ID ON test_all_objects(OBJECT_NAME);

Step3

Run below query and check explain plan

select * from test_all_objects where object_name='DUAL'

You can see it is using index

Step4

Make Index invisible

ALTER INDEX IDX_OBJ_NAME_ID INVISIBLE;

Step5

Run query again and check explain plan

select * from test_all_objects where object_name='DUAL'

You can see now optimizer has smartly ignored the index

Usages:

1. Test the removal of an index before dropping it
2. Add a column in middle of table
3. It can be used for disabling or making invisible a problematic (large in size) index causing performance issues. You can make it visible once after the issue has been addressed properly.
4. If you wanted to test some newly introduced index without affecting the other operations. The new index can put in invisible mode which will not affect the other normal operations with the table and complete the testing. Once the testing is completed and it is giving x times better performance the index can be make it to visible mode.
5. Invisible indexes are very useful during some certain performance issues. Suppose if you don’t wanted to use a large index during performance issue, prior to 11g we need to drop the index or make it unusable and test the scenario. But this 11g feature simplifies this method without dropping the index. Index rebuild is needed to make an unusable index to usable and it consumes time.
6. Invisible indexes can be used in only for specific applications without changing the execution plans for the other modules as it can be enabled or make it visible for session specific.
7. Another potential advantage of invisible index is the situations where we need an index as temporary purpose. An invisible index can be created temporarily for specific SQL statements without affecting the rest of the database.
8. Invisible indexes are alternative for dropping an index just for testing some performance issue scenarios.

Scenarios when invisible indexes are not invisible

1. Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance related issues when delete/update operations are performed on the parent records.
2. Invisible Indexes might not be so invisible after all when it comes to the CBO potentially using the index statistics of an Invisible Index to determine the correct cardinality of a query