Invisible Indexes (11g)

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.

create index <IndexName> on <TableName> (<columnNames>) invisible;

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

select index_name,VISIBILITY from user_indexes where index_name=<INDEX NAME>;

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.

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

alter index <IndexName> visible;

alter index <IndexName> invisible;

Generate the Explain Plan and we don't see that the Invisible index used.

Now alter the session parameter : optimizer_use_invisible_indexes=true

alter session set optimizer_use_invisible_indexes=true;

generate the Explain Plan and we can see that the Invisible index used.

Example :

Create table kk_invisibleIndex_test_tab ( x number, y number, z number);

insert into kk_invisibleIndex_test_tab
select  level,level+1,level+2
from    dual
connect by level <= 100000 ;
commit;

select * from kk_invisibleIndex_test_tab where z=300;

Plan
SELECT STATEMENT  ALL_ROWSCost: 105  Bytes: 156  Cardinality: 4    
    1 TABLE ACCESS FULL TABLE KK_INVISIBLEINDEX_TEST_TAB Cost: 105  Bytes: 156  Cardinality: 4
 
create index kk_Invisible_index on kk_invisibleIndex_test_tab (z) INVISIBLE;

select index_name,Index_type,table_name,visibility from user_indexes where index_Name='KK_INVISIBLE_INDEX';

INDEX_NAME                 INDEX_TYPE        TABLE_NAME                                     VISIBILITY
KK_INVISIBLE_INDEX       NORMAL             KK_INVISIBLEINDEX_TEST_TAB   INVISIBLE

select * from kk_invisibleIndex_test_tab where z=300;

Plan
SELECT STATEMENT  ALL_ROWSCost: 105  Bytes: 156  Cardinality: 4    
    1 TABLE ACCESS FULL TABLE KK_INVISIBLEINDEX_TEST_TAB Cost: 105  Bytes: 156  Cardinality: 4

alter session set optimizer_use_invisible_indexes = true;

select * from kk_invisibleIndex_test_tab where z=300;

Plan
SELECT STATEMENT  ALL_ROWSCost: 2  Bytes: 39  Cardinality: 1        
    2 TABLE ACCESS BY INDEX ROWID TABLE KK_INVISIBLEINDEX_TEST_TAB Cost: 2  Bytes: 39  Cardinality: 1    
        1 INDEX RANGE SCAN INDEX KK_INVISIBLE_INDEX Cost: 1  Cardinality: 1

alter session set optimizer_use_invisible_indexes = false;

select * from kk_invisibleIndex_test_tab where z=300;


Plan
SELECT STATEMENT  ALL_ROWSCost: 105  Bytes: 156  Cardinality: 4    
    1 TABLE ACCESS FULL TABLE KK_INVISIBLEINDEX_TEST_TAB Cost: 105  Bytes: 156  Cardinality: 4
   

alter index kk_Invisible_index VISIBLE;


select index_name,Index_type,table_name,visibility from user_indexes where index_Name='KK_INVISIBLE_INDEX';

INDEX_NAME                 INDEX_TYPE        TABLE_NAME                                     VISIBILITY
KK_INVISIBLE_INDEX       NORMAL             KK_INVISIBLEINDEX_TEST_TAB    VISIBLE