You can invisible an index instead of dropping for your any kind of test and make it visible when you need it in Oracle 11g. When you make any index invisible the query optimizer will disregard the index. You can make the index know to optimizer in two ways:

1.      Set initialization parameter optimizer_use_invisible_indexes to trues. When this parameter is true the optimizer treat the entire invisible index as normal index.

2.      If you want to make any particular index known to the optimizer then you need to user oracle hint.

Example: How to Invisible an index and make it visible again

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)



SQL> explain plan for select * from scott.emp where deptno=20;

Explained.

SQL> select * from TABLE(dbms_xplan.display);

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("DEPTNO"=20)

13 rows selected.


SQL> create index idx_dept on emp(deptno);

Index created.

SQL> explain plan for select * from scott.emp where deptno=20;

Explained.


SQL> select * from TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1877191900

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     5 |   190 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP      |     5 |   190 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DEPT |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------


   2 - access("DEPTNO"=20)

14 rows selected.

-- as sys user
SQL> select index_name, visibility from dba_indexes where index_name='IDX_DEPT'; 

INDEX_NAME                                     VISIBILITY
----------------------------------------------------------------------------------

IDX_DEPT                                        VISIBLE


SQL> alter index idx_dept invisible;

Index altered.

-- as sys user
SQL> select index_name, visibility from dba_indexes where index_name='IDX_DEPT';
INDEX_NAME                                       VISIBILITY
----------------------------------------------------------------------------------

IDX_DEPT                                         INVISIBLE


SQL> explain plan for select * from scott.emp where deptno=20;

Explained.


SQL> select * from TABLE(dbms_xplan.display);

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
   1 - filter("DEPTNO"=20)

13 rows selected.

To make the index visible again use the command:

SQL> alter index idx_dept visible;

Index altered.

-- as sys user
SQL> select index_name, visibility from dba_indexes where index_name='IDX_DEPT'; 

INDEX_NAME                                     VISIBILITY
----------------------------------------------------------------------------------

IDX_DEPT                                        VISIBLE

You can use hint to make this index known to the optimizer.

SQL> select /*+ index(idx_dept) */ * from scott.emp where deptno=20;

Explained.

You can set optimizer_use_invisible_indexes parameter true to make all the invisible index known to the optimizer. This is a dynamic parameter.

SQL> alter system set optimizer_use_invisible_indexes=true;