Invisible Index 11g, how to utilize it

Invisible index gives you the  ability to create an index on production without impacting the queries being fired by application but at the same time test the impact an index creation can cause. Invisible indexes are useful alternative to making an index unusable or to drop it. 

The optimizer ignores the index that are marked “Invisible” unless you set the initialization parameter “OPTIMIZE_USE_INVISIBLE_INDEXES” to TRUE. This parameter can be set both at a session level as well as system level.

Usage of Invisible Indexes

One can use invisible index for testing the impact of removing an index. Instead of dropping the index we can make it invisible and its effect.

One can speed up operations by creating invisible indexes for infrequent scenarios. Invisible index will make sure that the overall performance of the application is not affected.

Gives you the flexibility to have both b-tree (to guarantee unique PK) as well as bitmap indexes (on FK columns) in a data warehouse application.

How to create?

Multiple options – either mention “INVISIBLE” clause at the time of index creation or use

ALTER command to make an index “INVISIBLE”.

CREATE INDEX FNADVI.idx_FIELD1 ON FNADVI.T1 (FIELD1) NOLOGGING NOPARALLEL INVISIBLE;

OR

ALTER INDEX IDX_FIELD1 INVISIBLE;

To make the Index “VISIBLE”

ALTER INDEX IDX_FIELD1 VISIBLE;

A new column “VISIBILITY” is available in *_INDEXES data dictionary views to know if an index is visible or invisible.

Example:

select index_name,visibility from user_indexes where table_name='T1';

INDEX_NAME       VISIBILITY

IDX_FIELD1         INVISIBLE

T1_PK                 VISIBLE

     

explain plan for select * from t1 where FIELD1='test';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      |     2 |    26 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     2 |    26 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("FIELD1"='test')

Optimizer didn’t pickup the INDEX into consideration as it’s invisible.

Let’s make it visible in Session level and see the impact

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;

explain plan for select * from t1 where FIELD1='test';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 2538775970

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

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

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

|   0 | SELECT STATEMENT            |            |     2 |    26 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     2 |    26 |     2   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

2 - access("FIELD1"='test')