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 :-)
