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;