Invisible Indexes in ORACLE 11G

Use CREATE INDEX INVISIBLE command to create invisible indexes on table.

After creating invisible index on given table we have to use ALTER INDEX to make it VISIBLE.

By default INVISIBLE indexes are ignored by optimizer. OPTIMIZER_USE_INVISIBLE_INDEXES parameter need to set to TRUE to allow optimizer to use such indexes.

Let us create a test table and invisible index on it...

SQL> create table invisible_tab (column1 int);
Table created.

SQL> create index inv_idx on invisible_tab (column1) invisible;
Index created.

SQL> select index_name, visibility
  2   from DBA_INDEXES
  3  where index_name = 'INV_IDX';
INDEX_NAME                     VISIBILIT
------------------------------ ---------
INV_IDX                        INVISIBLE
SQL>

Now, let us insert sample data into the test table and observe whether invisible index used or not

SQL> begin
  2      for i in 1..100000 loop
  3          insert into invisible_tab values (i);
  4      end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from invisible_tab;
  COUNT(*)
----------
    100000
SQL> exec dbms_stats.gather_table_stats (USER, 'INVISIBLE_TAB', cascade => TRUE
);
PL/SQL procedure successfully completed.
SQL>

Let us observe the execution plan of the following statement

select * from invisible_tab where column1 = 678;



Now, let us set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE to use invisible index i.e created

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SQL> show parameter optimizer_use_invisible_indexes;
NAME                                 TYPE                                   VALUE
------------------------------------ -----------------------------------------
optimizer_use_invisible_indexes      Boolean                  TRUE
SQL>

select * from invisible_tab where column1 = 678;

In place of alter session above, we can also use following command to make use of index by optimizer.

alter index inv_idx visible;

use following command to convert index into invisible state

alter index inv_idx invisible;


The value of compatible parameter should be 11.0.0.0.0 or greater to use this concept

select * from
v$parameter
where name ='compatible'

If the value of compatible parameter is lower than 11.0.0.0.0 then we will get errors as follows

create index emp_idx1 on emp (empno) invisible
ORA-00406: COMPATIBLE parameter needs to be 11.0.0.0.0 or greater
ORA-00722: Feature "ALTER INDEX INVISIBLE, CREATE INDEX INVISIBLE"