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;