Invisible Indexes in Oracle 11g

Always wanted this – 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 emp_ename ON emp(ename)

      TABLESPACE users

      STORAGE (INITIAL 20K

      NEXT 20k

      PCTINCREASE 75)

      INVISIBLE;

ALTER INDEX index INVISIBLE;

To make the Index “VISIBLE”

ALTER INDEX index VISIBLE;

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

Example

SQL> create index indx_job on emp1(job);

Index created.

SQL> explain plan for select * from emp1 where job='CLERK';

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850
------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     4 |   348 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1     |     4 |   348 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_JOB |     4 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


SQL> explain plan for select * from emp1 where job='CLERK';

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 2226897347
------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   348 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP1 |     4 |   348 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;

Session altered.

SQL> select index_name,visibility from user_indexes where table_name='EMP1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
INDX_JOB                       INVISIBLE

SQL>  explain plan for select * from emp1 where job='CLERK';

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     4 |   348 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1     |     4 |   348 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_JOB |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------