Invisible Index

Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].

Syntax:

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

Examples:

SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes 
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_invisible_indexes boolean FALSE



SYSTEM@orcl11g>desc test

Name Null? Type

----------------------------------------------------- -------- ------------

N NUMBER

NAME VARCHAR2(10)



SYSTEM@orcl11g>create index idx1 on test(n) invisible;

Index created.



SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes

2 where visibility = 'INVISIBLE';



OWNER INDEX_NAME VISIBILIT

------------------------------ ------------------------------ ---------

SYSTEM IDX1 INVISIBLE



SYSTEM@orcl11g>select * from test where n=5;



N NAME

---------- ----------

5 H



Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020



--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------



SYSTEM@orcl11g>alter index idx1 visible;

Index altered.



SYSTEM@orcl11g>select * from test where n=5;



N NAME

---------- ----------

5 H



Execution Plan

----------------------------------------------------------

Plan hash value: 1175071277



------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------

Use 1: Adding an index

You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.

Use 2: Dropping an index

You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.