Invisible Index with 11G

Index with Performance it is always big issues with performance experts.

FULL TABLE SCAN is good

or

INDEX SCAN is good

Mr. Tom kyte Oracle Expert said "FULL TABLE SCAN is not bad" always and "INDEX SCAN is not good" always.

Now with 11g we can test both performance means with or without index WITHOUT DROPPING exisiting index on table.

Before 11g if we don't want to use INDEX then we need to drop it.

but now in 11G we no need to drop it we just set INVISIBLE so optimizer will ignore index during query execution.

SQL> create index emp_ename ON emp(ename) visible;
Index created.
SQL> set autotrace traceonly

SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 1221021741
------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 1 6 1 (0) 00:00:01

* 1 INDEX RANGE SCAN EMP_ENAME 1 6 1 (0) 00:00:01

------------------------------------------------------------------------------
Predicate Information (identified by operation id):

---------------------------------------------------
1 - access("ENAME"='SMITH')
Statistics

---------------------------------------------------------- 1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> --Now check performance without INDEX means make index INVISIBLE for optimizer

SQL> --for that we need to set INDEX "INVISIBLE" through below command

SQL> alter index emp_ename INVISIBLE;
Index altered.

NOTE: You can use above statement to make it VISIABLE again just replace INVISIBLE to VISIBLE.

SQL> --now check same query WITHOUT index.

SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 3956160932
--------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 1 6 3 (0) 00:00:01

* 1 TABLE ACCESS FULL EMP 1 6 3 (0) 00:00:01

--------------------------------------------------------------------------
Predicate Information (identified by operation id)

:---------------------------------------------------
1 - filter("ENAME"='SMITH')
Statistics

---------------------------------------------------------- 218 recursive calls

0 db block gets

49 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> --This time optimizer will not use INDEX in query execution.

There is one new parameter "OPTIMIZER_USE_INVISIBLE_INDEXES" which we can use for INVISIBLE index. Just take above example here I set index status INVISIBLE to optimizer will not use index.

Now if we set OPTIMIZER_USE_INVISIBLE_INDEXES=true then optimizer will use INVISIBLE indexes.

When can set this parameter at SESSION or SYSTEM level.

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;
Session altered.
SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 1221021741
------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 1 6 1 (0) 00:00:01

* 1 INDEX RANGE SCAN EMP_ENAME 1 6 1 (0) 00:00:01

------------------------------------------------------------------------------
Predicate Information (identified by operation id)

:---------------------------------------------------
1 - access("ENAME"='SMITH')

Query about INVISIBLE indexes

USER_INDEXES, ALL_INDEXES, DBA_INDEXES

SQL> select index_name,visibility from user_indexes 2 where index_name='EMP_ENAME';
INDEX_NAME VISIBILIT

------------------------------ ---------EMP_ENAME INVISIBLE