Invisible Indexes

11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default.

Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.

SQL> create table teste_ii (a number, b varchar2(10));

SQL> create index idx_invisible on teste_ii(id);

begin

for i in 1..20

loop

insert into teste_ii(a,b) values(i,'TESTE'||i);

end loop;

end;

/

commit;

SQL> select * from teste_ii where a=10;

A B

---------- ----------

10 TESTE10

SQL> explain plan for

2 select * from teste_ii where a=10;

Explained

SQL> sELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 726495292

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0

| 1 | TABLE ACCESS BY INDEX ROWID| TESTE_II | 1 | 10 | 2 (0

|* 2 | INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 1 (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"=10)

Set index to invisible:

SQL> alter index denilson.idx_invisible invisible;

Index altered

SQL> explain plan for

2 select * from teste_ii where a=10;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2149228497

------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TESTE_II | 1 | 10 | 4 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("A"=10)

Force the optmizer to get the index in the your session:

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered

The index still invisible.

SQL> select index_name,table_name,status,VISIBILITY from user_indexes where index_name = 'IDX_INVISIBLE';

INDEX_NAME TABLE_NAME STATUS VISIBILITY

--------------------------- ----------------------- ----------- ----------

IDX_INVISIBLE TESTE_II VALID INVISIBLE

SQL> explain plan for

2 select * from teste_ii where a=10;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 726495292

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0

| 1 | TABLE ACCESS BY INDEX ROWID| TESTE_II | 1 | 10 | 2 (0

|* 2 | INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 1 (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"=10)