Invisible Index

What is Invisible Index?

 * Invisible indexes will be ignored by Optimizer.
 * Help to see whether index should be dropped before actually dropping it.

Demo
SQL> create index ENAME_IDX on EMP(ENAME); Index created
 * Create a new index.

SQL> select index_name, VISIBILITY from user_indexes where index_name='ENAME_IDX';
 * By default, the index is VISIBLE. To create an INVISIBLE index directly, use create index ENAME_IDX on EMP(ENAME) INVISIBLE;

INDEX_NAME                    VISIBILITY -- -- ENAME_IDX                     VISIBLE

SQL> explain plan for select ENAME from EMP where ENAME='MILLER'; Explained SQL> select * from table(dbms_xplan.display);
 * Explain plan: expected INDEX will be used.

PLAN_TABLE_OUTPUT

Plan hash value: 4091927879 -- -- -- Predicate Information (identified by operation id): ---  1 - access("ENAME"='MILLER')
 * Id | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 * 0 | SELECT STATEMENT |          |     1 |     7 |     1   (0)| 00:00:01 |
 * 1 | INDEX RANGE SCAN| ENAME_IDX |     1 |     7 |     1   (0)| 00:00:01 |

SQL> alter index ENAME_IDX invisible;
 * Make index INVISIBLE. The Optimizer will IGNORE this index.

Index altered SQL> select index_name, VISIBILITY from user_indexes where index_name='ENAME_IDX';

INDEX_NAME                    VISIBILITY -- -- ENAME_IDX                     INVISIBLE

SQL> explain plan for select ENAME from EMP where ENAME='MILLER';
 * Explain plan: expected index will NOT be used.

Explained SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 2872589290 -- -- -- Predicate Information (identified by operation id): ---  1 - filter("ENAME"='MILLER')
 * Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 * 0 | SELECT STATEMENT |      |     1 |     6 |     3   (0)| 00:00:01 |
 * 1 | TABLE ACCESS FULL| EMP  |     1 |     6 |     3   (0)| 00:00:01 |

SQL> show parameter optimizer_use_invisible_indexes NAME                                TYPE        VALUE --- -- optimizer_use_invisible_indexes     boolean     FALSE
 * Oracle has the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES. Its default is FALSE. This parameter can be enabled at the SESSION level.

SQL> alter session set optimizer_use_invisible_indexes=TRUE; Session altered
 * Enable the use of index even though it is INVISIBLE.

SQL> explain plan for select ENAME from EMP where ENAME='MILLER'; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
 * Explain Plan: now expected index will be used even though it is INVISIBLE.

Plan hash value: 4091927879 -- -- -- Predicate Information (identified by operation id): ---  1 - access("ENAME"='MILLER')
 * Id | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 * 0 | SELECT STATEMENT |          |     1 |     6 |     1   (0)| 00:00:01 |
 * 1 | INDEX RANGE SCAN| ENAME_IDX |     1 |     6 |     1   (0)| 00:00:01 |