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

• Create a new index.

SQL> create index ENAME_IDX on EMP(ENAME); 
Index created

• By default, the index is VISIBLE. To create an INVISIBLE index directly, use create index ENAME_IDX on EMP(ENAME) INVISIBLE;

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

INDEX_NAME                     VISIBILITY
------------------------------ ----------
ENAME_IDX                      VISIBLE

• Explain plan: expected INDEX will be used.

SQL> explain plan for select ENAME from EMP where ENAME='MILLER';
 Explained
 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4091927879
------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ENAME"='MILLER')

• Make index INVISIBLE. The Optimizer will IGNORE this index.

SQL> alter index ENAME_IDX invisible;

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

INDEX_NAME                     VISIBILITY
------------------------------ ----------
ENAME_IDX                      INVISIBLE

• Explain plan: expected index will NOT be used.

SQL> explain plan for select ENAME from EMP where ENAME='MILLER';

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='MILLER')

• Oracle has the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES. Its default is FALSE. This parameter can be enabled at the SESSION level.

SQL> show parameter optimizer_use_invisible_indexes
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

• Enable the use of index even though it is INVISIBLE.

SQL> alter session set optimizer_use_invisible_indexes=TRUE;
 
Session altered

• Explain Plan: now expected index will be used 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
--------------------------------------------------------------------------------
Plan hash value: 4091927879
------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ENAME"='MILLER')