Oracle11g: InvisibleIndexes

Oracle11g allows you to mark indexes as invisible. oracle maintains invisibleindex like other indexes, but invisibleindex cannot be optimizer by default.

Oracle 11g allows the index to be marked as invisible. oracle maintains the invisible index like other indexes, but the invisible index cannot be optimizer by default.

Oracle 11g allows the index to be marked as invisible. oracle maintains invisible indexes like other indexes, but the default invisible index cannot be used by the optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE is explicitly set (you can alter system/session ). you can specify the INVISIBLE keyword or the alter index Command to create an INDEX.

Create index idx_name on table_name (column_name) INVISIBLE;
Alter index idx_name INVISIBLE;
Alter index idx_name VISIBLE;

Demo:

SQL> create table ii_tab (id number );

Table created.

SQL> begin
For I in 1 .. 10000 loop
Insert into ii_tab values (I );
End loop;
Commit;
End;
/

PL/SQL procedure successfully completed.

SQL> create index ii_tab_id on ii_tab (id) invisible;

Index created.

SQL> exec dbms_stats.gather_table_stats (USER, 'II _ tab ', cascade => TRUE );

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select * from ii_tab where id = 9999;

ID
----------

Execution Plan
----------------------------------------------------------
Plan hash value: 2057286804

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 7 (0) | 00:00:01 |
| * 1 | table access full | II_TAB | 1 | 4 | 7 (0) | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------
-Filter ("ID" = 9999)

Statistics
----------------------------------------------------------
Recursive cballs
Db block gets
Consistent gets
Physical reads
Redo size
Bytes sent via SQL * Net to client
Bytes encoded ed via SQL * Net from client
SQL * Net roundtrips to/from client
Sorts (memory)
Sorts (disk)
Rows processed

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> select * from ii_tab where id = 9999;

ID
----------

Execution Plan
----------------------------------------------------------
Plan hash value: 81730945

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 1 (0) | 00:00:01 |
| * 1 | index range scan | II_TAB_ID | 1 | 4 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------
-Access ("ID" = 9999)

Statistics
----------------------------------------------------------
Recursive cballs
Db block gets
Consistent gets
Physical reads
Redo size
Bytes sent via SQL * Net to client
Bytes encoded ed via SQL * Net from client
SQL * Net roundtrips to/from client
Sorts (memory)
Sorts (disk)
Rows processed

SQL> alter session set optimizer_use_invisible_indexes = false;

Session altered.

It can be seen that even if the index is set to invisible, when optimizer_use_invisible_indexes is true, the optimizer still takes the index instead of the full table.

Compare alter index... UNUSABLE. The optimizer does not use the unusable index and requires REBUILD.