Invisible Indexes - Oracle 11g New Feature

Before 11g, Oracle didn’t have the facility to make an Index Invisible or visible……

Invisible Indexes, as the name imply is invisible to the optimizer except when we explicitly make it visible. When an index is made invisible it remains unavailable for the optimizer while running execution plans for queries. While running execution plans the optimizer decides which indexes to be used for faster execution.

This index is maintained like normal index but the optimizer ignores it unless OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE. Making an index invisible won’t change any of the properties of the index, it remains the same (For ex. You will receive error if you try to create invisible index on already indexed column).

WE can check if INDEX is visible or not using VISIBILITY column from the data dictionary view DBA_INDEXES.

SQL>select index_name, visibility from dba_indexes where index_name=’index_inv’;

INDEX_NAME     VISIBILITY

—————————      —————————  

index_inv      VISIBLE

How to create Invisible Indexes?

By default all indexes created are visible. We have to specify the “invisible” keyword to make an index invisible.

CREATE INDEX index_name_inv ON table_name(column) INVISIBLE;

ALTER INDEX index_name_inv  INVISIBLE;

ALTER INDEX index_name_inv  VISIBLE;

Need of Invisible Indexes ??

• To test the removal of an index before dropping it or test Index Performance!!!

While checking for Database performance, the indexes are made unusable for the activity period. If the performance is negatively  impacted then the index needs to be rebuilt .From Oracle 11g we can make the index as invisible , check for the performance and then make the index  visible if the performance is badly affected. This reduces the expense of Recreating or Rebuilding the index

• The other use of Invisible Indexes is when we want to create an indextemporarily (not to be a part of database).

Note: The invisible index will be unavailable to the optimizer unless you include an index hint in the query

******Read more about Indexes here,,,,,,What is An index