8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Invisible Indexes in Oracle Database 11g Release 1

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

create index index_name on table_name(column_name) invisible;

alter index index_name invisible;
alter index index_name visible;

Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of adding or dropping an index.

The following script creates and populates a table, then creates an invisible index on it.

-- drop table ii_tab purge;

create table ii_tab as
select level as id
from   dual
connect by level <= 10000;

create index ii_tab_id on ii_tab(id) invisible;

exec dbms_stats.gather_table_stats(null, 'ii_tab', cascade=> true);

The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

column index_name format a30
column visibility format a10

select index_name, visibility
from   user_indexes;

INDEX_NAME                     VISIBILITY
------------------------------ ----------
II_TAB_ID                      INVISIBLE

SQL>

A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

select * from ii_tab where id = 9999;

select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| II_TAB |     1 |     4 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.

alter session set optimizer_use_invisible_indexes=true;

select * from ii_tab where id = 9998;

select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| II_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

alter session set optimizer_use_invisible_indexes=false;
alter index ii_tab_id visible;

select * from ii_tab where id = 9997;

select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| II_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Making the index invisible again means it is no longer available to the optimizer.

alter index ii_tab_id invisible;

select * from ii_tab where id = 9996;

select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| II_TAB |     1 |     4 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

For more information see:

Hope this helps. Regards Tim...

Back to the Top.