11G new feature - Invisible Indexes

Sometimes we need to predict what will cause dropping any index on a table or creating new index on it.

That is where 11G Invisible Index makes life easier:

SQL> create table myt1 as select * from dba_objects;

Table created.

SQL> select count(*) from myt1;

COUNT(*)
----------
72046

SQL> set autotrace traceonly explain

SQL> select count(*) from myt1 where object_id > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 967654561

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   280   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| MYT1 | 72791 |   924K|   280   (1)| 00:00:04 |
---------------------------------------------------------------------------

As you see, Oracle is doing full scan. Now lets create index on myt1:

SQL> create index myt1$object_id on myt1(object_id);

Index created.

SQL> select count(*) from myt1 where object_id > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1289521697

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

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT      |                |     1 |    13 |    45   (0)| 00
:00:01 |

|   1 |  SORT AGGREGATE       |                |     1 |    13 |            |
|

|*  2 |   INDEX FAST FULL SCAN| MYT1$OBJECT_ID | 72791 |   924K|    45   (0)| 00
:00:01 |

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

Now, Oracle is going to full scan newly created index - So, for every end user doing the same job, Oracle will do the same thing - But who knows, may be full scan is better then fast full scanning index. That is it is better to test execution plan before it is going to production. Lets make newly created index to be invisible :


SQL> alter index myt1$object_id invisible;

Index altered.

SQL>  select count(*) from myt1 where object_id > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 967654561

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   280   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| MYT1 | 72791 |   924K|   280   (1)| 00:00:04 |
---------------------------------------------------------------------------



Yes, optimizer is not using index event it is there...
If you want it to be used, change OPTIMIZER_USE_INVISIBLE_INDEXES=true.
It is dynamic and can be altered on session/system level.

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL> select count(*) from myt1 where object_id > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1289521697

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

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT      |                |     1 |    13 |    45   (0)| 00
:00:01 |

|   1 |  SORT AGGREGATE       |                |     1 |    13 |            |
|

|*  2 |   INDEX FAST FULL SCAN| MYT1$OBJECT_ID | 72791 |   924K|    45   (0)| 00
:00:01 |

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