First, create a simple table and associated index.

SQL> CREATE TABLE invisible_bowie 
     AS SELECT rownum id, sysdate-10000+mod(rownum,10000) date_field 
        FROM dual CONNECT BY LEVEL <=1000000;

Table created.

SQL> CREATE INDEX invisible_bowie_i ON invisible_bowie(date_field);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE',tabname=>'INVISIBLE_BOWIE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.


By default, indexes are created as "VISIBLE" in line with previous behaviour.


SQL> SELECT index_name, visibility FROM user_indexes WHERE index_name = 'INVISIBLE_BOWIE_I';

INDEX_NAME                     VISIBILITY
------------------------------ ---------------
INVISIBLE_BOWIE_I              VISIBLE


As the index is visible, it can be considered and used by the CBO ...



SQL> SELECT * FROM invisible_bowie WHERE date_field > sysdate - 1;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2773462488
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   100 |  1200 |    103  (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| INVISIBLE_BOWIE   |   100 |  1200 |    103  (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | INVISIBLE_BOWIE_I |   100 |       |      3  (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


However, 11g allows us to make indexes invisible ...


SQL> ALTER INDEX invisible_bowie_i INVISIBLE;

Index altered.

SQL> SELECT index_name, visibility FROM user_indexes WHERE index_name = 'INVISIBLE_BOWIE_I';

INDEX_NAME                     VISIBILITY
------------------------------ ---------------
INVISIBLE_BOWIE_I              INVISIBLE


Now the CBO can't "see" the index as it's invisible ...


SQL> SELECT * FROM invisible_bowie WHERE date_field > sysdate - 1;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3921283736
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   100 |  1200 |   719   (5)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| INVISIBLE_BOWIE |   100 |  1200 |   719   (5)| 00:00:09 |
-------------------------------------------------------------------------------------


Oracle chooses a Full Table Scan as there's no available, visible index ...

However, any specific sessions can be altered so they can "see" these invisible indexes. 


SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered.

SQL> SELECT * FROM invisible_bowie WHERE date_field > sysdate - 1;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2773462488
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   100 |  1200 |    103  (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| INVISIBLE_BOWIE   |   100 |  1200 |    103  (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | INVISIBLE_BOWIE_I |   100 |       |      3  (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


Indexes can also be altered and made visible to everyone ...


SQL> ALTER INDEX invisible_bowie_i VISIBLE;

Index altered.

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = false;

Session altered.

SQL> SELECT * FROM invisible_bowie WHERE date_field > sysdate - 1;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2773462488
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   100 |  1200 |    103  (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| INVISIBLE_BOWIE   |   100 |  1200 |    103  (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | INVISIBLE_BOWIE_I |   100 |       |      3  (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------