Invisible Indexes

This article presents new features of 11g invisible indexes.

  • new index attribute VISIBLE/INVISIBLE
  • new instance/session parameter OPTIMIZER_USE_INVISIBLE_INDEXES=(TRUE|FALSE) default FALSE – overrides index attribute INVISIBLE when set to TRUE
  • INVISIBLE indexes maintained during DML statements unlike unusable indexes
  • you can test impact of removal of a index before dropping it
  • you can test impact of adding new index or add it for some specific actions
  • new VISIBILITY column in DBA/ALL/USER_INDEXES

Example

--optimizer ignores invisible indexes
alter session set optimizer_use_invisible_indexes=false;

--drop table test
drop table test;

--create test table
create table test
(id number);

--create invisible index
create index test_idx on test(id) invisible;

--insert data to test table
begin
  for i in 1..100
  loop
    insert into test values(i);
  end loop;
  commit;
end;
/

--gather stats on test table
begin
  dbms_stats.gather_table_stats(user, 'TEST', cascade=>true);
end;
/

--check explain plan
explain plan for
select * from test where id=1;

select * from table(dbms_xplan.display);

/*
--------------------------------------------------------------------------                                                                                                                                                                                                                                   
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
-------------------------------------------------------------------------- 
*/

--optimizer uses invisible indexes
alter session set optimizer_use_invisible_indexes=true;

--check explain plan
explain plan for
select * from test where id=1;

select * from table(dbms_xplan.display);

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

--switch index to visible
alter index test_idx visible;

--optimizer skips invisible indexes
alter session set optimizer_use_invisible_indexes=false;

--check explain plan
explain plan for
select * from test where id=1;

select * from table(dbms_xplan.display);

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

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *