Invisible Indexes in Oracle 11g

Have you ever wished you could create an index that was visible to only your session so you could test its effect before unleashing it on production? Or perhaps you wanted to make an index disappear for a day to see if the query really was slower. Or maybe you wanted to create an index that was only used by a monthly job. In 11g, Oracle has done just that – created the ability to make an index invisible or visible. DBA_INDEXES has a new column named VISIBILITY that indicates the status of the index. We will look at that column to determine the status of the index. Also there is a new initialization parameter named optimizer_use_invisible_indexes which determines whether all invisible indexes are visible to the optimizer or not.

So, if we first create an index named hg (note key word INVISIBLE).

SQL> create index hg on tab1897(wells) invisible;

We can make it visible to our session for testing. This can be done by either setting the initparm optimizer_use_invisible_indexes to true or by using a hint in the query.

SQL> alter session set optimizer_use_invisible_indexes=true;
 SQL> select /*+ index  (table1897 hg) */  hg
 from tab1897 where hg= ‘AUTHOR’;

Once the testing is completed and it is determined that this is a great index for the system, it can be made visible to everyone.

SQL>alter index hg visible;
 
SQL>select index_name, visibility from dba_indexes where index_name=’HG’;
 

INDEX_NAME      VISIBILITY


————–  ———
 HG              VISIBLE

Similarly, if we decide that we would like to remove an index might but want to test it first, the index can be made invisible.

SQL>alter index hg invisible;
 
SQL>select index_name, visibility from dba_indexes where index_name=’HG’;
 INDEX_NAME      VISIBILITY
 ————— ———
 HG              INVISIBLE

Additionally, the invisible indexes can all be made visible by the system modifiable initialization parameter optimizer_use_invisible_indexes.

SQL> alter system set optimizer_use_invisible_indexes=true;

When an index is made invisible, it is invisible only to the optimizer. This means it won’t be used for any execution plans unless there is a hint specified, the session is set to use invisible indexes, or if the database is set to use all invisible indexes. The index is not invisible to DML which will be update and delete from it normally. So although an invisible index won’t be used in an execution plan, the cost of updating it still applies. The real benefit to the invisible index comes when you want to test an index without affecting all your other queries. This will improve the testing process immensely. Also if an index is made invisible and it is determined that response was better with the index, it can be brought back without the costly rebuild process.

Note: SolarWinds Database Performance Analyzer has the capability to identify index bottlenecks.