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