Oracle Invisible Index...

Oracle 11g introduces a new feature for indexes, invisible indexes, that is useful in 
several different situations.  


An invisible index is an index that is maintained by the database but 
ignored by the optimizer unless explicitly specified.  


The invisible index is an alternative to dropping or making an index unusable.  


This feature is also functional when certain modules of an application 
require a specific index without affecting the rest of the application. 


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, we have an 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 index.  
This can minimize the period of performance degradation, while also preventing an 
expensive operation to rebuild or recreate an index.


To make an index invisible:


ALTER INDEX index_name INVISIBLE;


To make an index visible:


ALTER INDEX index_name VISIBLE;


Data Dictionary:
Query the column "VISIBILITY" from the data dictionary view dba_indexes 
to check the current status of an index. 


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. 
Creating a visible index for this same purpose would cause the optimizer to consider 
the new index for all execution plans on that object. 


Create an invisible index on order_lines.attribute7:


create index claim_dtl_inv1 
    on claim_details(col2) 
INVISIBLE;


This one query can be modified to explicitly use the invisible index with a hint:


select /*+ INDEX (claim_details claim_dtl_inv1) */ 
       claim_no#, claim_amt 
  from claim_details
 where col2 = 'some_value_here';


If the application code cannot be modified, it is possible to instruct the optimizer to 
include invisible indexes at the session level:


alter session set optimizer_use_invisible_indexes = true;


NOTE: Keep in mind that rebuilding an invisible index will make it visible. 


Init Parameter:
By Default optimizer_use_invisible_indexesis set to FALSE and thus all INVISIBLE indexes are ignored by COB. 
then the CBO will see the index. By default, the parameter is set to FALSE so that the CBO ignores 
the invisible index when using execution plans. 


Invisible indexes are an attractive feature for the process of dropping an index. 
They are also useful when a specific application needs the benefit of a temporary 
index without impacting the database on a wider scale. Since the database must still 
maintain an invisible index for all DML operations, invisible indexes should not be 
used unless necessary. Though they should be removed once their purpose has been served, 
invisible indexes offer substantial advantages for short-term solutions.  


The beauty of using the invisible index feature for Oracle 11g is that it allows the developer 
and DBA staff to test performance for the index without the risk of dropping an index which can be 
time consuming and a potential risk to the production environment.