Invisible Indexes in Oracle 11g

An invisible index is an index that is ignored by the optimizer unless we explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.   One use of the invisible index feature is to test the removal of an index before dropping it.  Prior to 11g, this was typically achieved by making an index unusable during a set period of time.  During this observation period, the DBA would monitor the database performance to determine whether or not to drop the index.  If performance was negatively affected, the index would need to be rebuilt before it could be used again.  Beginning with Oracle 11g, the DBA has the option of making the index  invisible as opposed to unusable during this observation period.  If performance degradation is observed, the index can be made visible again without having to rebuild the indexes.

Another potential use for invisible indexes is in situations where specific applications require an index temporarily.  An index can be created as invisible to allow specific SQL statements to use the index while leaving the rest of the database unaffected.  

The effective usage of an index is always a big question. There are instances in production environments, where an index may help the performance of just a single query, while it can degrade the performance of a number of queries. Always the Optimizer can’t guarantee the best suitable index required for the query in an environment which is cluttered by too many indexes on a table. Below is  demo of invisble Indexes 

SQL> create table test1 as select * from all_objects;
Table created.

SQL> select count(*) from test1;
COUNT(*)
-------------
 71480

SQL> create index obj_idx on test1(object_name) invisible;
Index created.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
-------------------                    --------------
OBJ_IDX                             INVISIBLE

There is a new initialization parameter modifiable at system or session level called 

OPTIMIZER_USE_INVISIBLE_INDEXES. This parameter is FALSE as default, meaning the optimizer will not consider invisible indexes when evaluating alternative execution plans.

SQL> sho parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME                                                          TYPE          VALUE
---------------------------------------                 -----------      -------------
optimizer_use_invisible_indexes              boolean         FALSE

We can alter an existing index to become invisible or visible.

SQL> alter index OBJ_IDX visible;
Index altered.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
-------------------                 --------------
OBJ_IDX                            VISIBLE

SQL> alter index OBJ_IDX invisible;
Index altered.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
--------------------                   --------------
OBJ_IDX                            INVISIBLE

We can see that the invisible index is not considered by the optimizer using a 10053 trace.

SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.

sql> explain plan for select * from TEST1 where OBJECT_NAME='ALL_OBJECTS';
Explained.

***************************************
BASE STATISTICAL INFORMATION
***************************************
Table Stats::
  Table: OBJ$  Alias: ITO
    #Rows: 73375  #Blks:  905  AvgRowLen:  83.00
Index Stats::
  Index: I_OBJ1  Col#: 1 3 7
    LVLS: 1  #LB: 206  #DK: 73375  LB/K: 1.00  DB/K: 1.00  CLUF: 963.00
UNUSABLE

SQL> explain plan for select * from test1 where object_name='ALL_OBJECTS';
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 4122059633
--------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |   194 |   286   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     2 |   194 |   286   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("OBJECT_NAME"='ALL_OBJECTS')

13 rows selected.

We can change the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to see if the index will be used.

SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

SQL> explain plan for select * from test1 where object_name='ALL_OBJECTS';
Explained.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 316 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 316 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='ALL_OBJECTS')
Note   ::    dynamic sampling used for this statement

14 rows selected.


Enjoy    :-)