While generating execution plans for queries, the optimizer decides at run time which indexes can be used to make query execute faster. The run time cost based optimizer is very efficient in its decisions. However, in the normal day to day operations there may be some situations where you would not like optimizer to use certain indexes. The option to restrict optimizer from using any index was not available until 11g, but now, you can do it by using invisible indexes. The optimizer will only use the invisible index, when you ask it to do so in your SQL statement, otherwise the index will normally remain hidden from optimizer.
The first question which comes to the mind is why anyone would like to have invisible indexes or why indexes should be made invisible, when they are created to aid optimizer in first place. Below are two use cases among many that are valid for invisible indexes usage.
To test an index’s performance
You want to create an index but you are not sure if it will be useful or not, and want to test it before optimizer starts using it permanently in execution plans. You can create that index as invisible and test it by referring its name in SQL statements and once you feel that, it is good for performance you can make it visible, otherwise you can drop it.
The other is that you want to create and use an index temporarily and have no plans of making it permanent part of database. You can create that index as invisible.
The invisible index will be unavailable to the optimizer unless you include an index hint in the query. So for any DML operations on the table, Oracle will also update the invisible index in the usual way it does for the visible indexes.
Creating and Managing Invisible Indexes
By default all indexes created are visible. You need to specify the “invisible” keyword to make an index invisible.
CREATE INDEX TEST_IDX ON TEST_TBL (TEST_COL) INVISIBLE;
You can also specify the tablespace where you want to create an index just like you do for visible indexes.
CREATE INDEX TEST_IDX2 ON TEST_TBL (TEST_COL2) INVISIBLE TABLESPACE EXAMPLE;
You can also change an existing visible index into invisible index. You will use the ALTER INDEX command to do so.
ALTER INDEX TEST_IDX3 INVISIBLE;
Once this command is executed, the optimizer will no longer be able to see the index and will not use it in generating execution plans. You can change an invisible index into visible index in the same way.
ALTER INDEX TEST_IDX VISIBLE;
As mentioned above, making an index invisible only hides it from the optimizer and the rest of the properties remain the same. Just like you cannot create another index on an already indexed column, you will not be able to create an invisible index on an already indexed column. If you try do so you will receive an error.
To view information on invisible indexes, you can query the DBA_INDXES view. This view has a new column in 11g named “VISIBILITY” which shows whether the index is visible or invisible.
SELECT INDEX_NAME, VISIBILITY
Optimizer and Invisible Indexes
By default all the invisible indexes remain unavailable to optimizer. Oracle provides an initialization parameter named “OPTIMIZER_USE_INVISIBLE_INDEXES” to control the behavior of the optimizer in regards to invisible indexes. By default this parameter has a value “FALSE” which means that optimizer cannot use any invisible indexes. Setting this parameter’s value to “TRUE” will make all invisible indexes visible to optimizer. You may want to turn this on if you have a requirement to temporarily make all invisible indexes visible.
ALTER SYSTEM SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
You can change it back to the default value.
ALTER SYSTEM SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
You can also, set this at the session level. All invisible indexes will be visible to the optimizer during that specific session.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Additionally, if the requirement is to make only one particular invisible index visible then you can do that with a query hint option. For example the following query will force the optimizer to use TEST_IDX index even though the index is invisible.
SELECT /*+ INDEX (TEST_TBL TEST_IDX) */
Although the index on TEST_COL is invisible but above query will not scan the whole table and will use the invisible index to return back results.
Further Reading Delete Specified Backups and Archive Logs.