Invisible indexes in 11G

11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default. Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.

We can create an index as invisible or we can alter an index to become invisible.

YAS@11G>drop table t;

Table dropped.

YAS@11G>create table t as select * from all_objects;

Table created.

YAS@11G>create index t_ind on t(object_name) invisible;

Index created.

USER_INDEXES has a new column named VISIBILITY to indicate whether the index is visible or not.

YAS@11G>select index_name,VISIBILITY from user_indexes where index_name='T_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND                          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.

YAS@11G>show parameter visible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

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

YAS@11G>alter index t_ind visible;

Index altered.

YAS@11G>select index_name,VISIBILITY from user_indexes where index_name='T_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND                          VISIBLE

YAS@11G>alter index t_ind invisible;

Index altered.

YAS@11G>select index_name,VISIBILITY from user_indexes where index_name='T_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND                          INVISIBLE

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

YAS@11G>alter session set events '10053 trace name context forever, level 1';

Session altered.

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

In the trace file the index is marked as UNUSABLE and is not considered.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
 Table:  T  Alias:  T  (NOT ANALYZED)
   #Rows: 80129  #Blks:  981  AvgRowLen:  100.00
Index Stats::
 Index: T_IND  Col#: 2
   LVLS: 2  #LB: 323  #DK: 38565  LB/K: 1.00  DB/K: 1.00  CLUF: 33236.00
   UNUSABLE

And the plan is:

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

YAS@11G>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |  1738 |   268   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    11 |  1738 |   268   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

17 rows selected.

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

YAS@11G>alter session set optimizer_use_invisible_indexes=true;

Session altered.

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

YAS@11G>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 |   202 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   202 |     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"='STANDARD')

14 rows selected.

YAS@11G>alter session set optimizer_use_invisible_indexes=false;

Session altered.

If we make the index visible we see that the index is considered and used.

YAS@11G>alter index t_ind visible;

Index altered.

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

YAS@11G>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"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

18 rows selected.

Trying to gather statistics on an invisible index using either gather_index_stats or gather_table_stats with the cascade option gets an error.

YAS@11G>alter index t_ind invisible;

Index altered.

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');
BEGIN dbms_stats.gather_index_stats(user,'T_IND'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1

YAS@11G>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
BEGIN dbms_stats.gather_table_stats(user,'T',cascade=>true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

YAS@11G>alter index t_ind visible;

Index altered.

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');

PL/SQL procedure successfully completed.

YAS@11G>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

I did not search if this is a bug or intended behaviour but we need a way to collect statistics for an invisible index, otherwise how can we test if it will be used or not? There is a way to collect statistics for an invisible index, which is to set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES.

YAS@11G>alter session set optimizer_use_invisible_indexes=true;

Session altered.

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');

PL/SQL procedure successfully completed.

YAS@11G>alter session set optimizer_use_invisible_indexes=false;

Session altered.