Invisible index is introduced in oracle 11g. As the name suggest, this type of index will be ignored by database optimizer, as if it doesn’t exists.
EXAMPLE:
— CREATE AN INDEX ( BY DEFAULT THE INDEX WILL BE A VISIBLE ONE)
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
SQL> select count(*) from emp;
COUNT(*)
----------
90323
SQL> create index EMP_ID1 on DBACLASS.EMP(OBJECT_ID);
Index created.
-- Check the visibility of the index
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 VISIBLE
Check the explain plan:
SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 853747123
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| EMP_ID1 | 1 | 5 | 1 (0)| 00:00:01 | -->>> INDEX USED
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.
As expected optimizer is using the index for the query.
When index is INVISIBLE:
Now make the INDEX invisible and check the explain plan for the same query.
SQL> alter index EMP_ID1 invisible;
Index altered.
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 INVISIBLE
SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 149 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 5 | 149 (2)| 00:00:01 | -->> FULL TABLE SCAN
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=1)
14 rows selected.
We can see, it is bypassing the index scan and doing full table scan, Despite the index is present.
Because the index is invisible.
Create an invisible index directly:
SQL> create index EMP_IDINV on DBACLASS.EMP(OBJECT_ID) invisble; SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_IDINV'; INDEX_NAME VISIBILIT ------------------- --------- EMP_IDINV INVISIBLE
optimizer_use_invisible_indexes paramter and invisible index There is an parameter
optimizer_user_invisible_indexes, which is by default set to FALSE, means, optimizer will ignore all the invisible indexes in the database.
SQL> show parameter invi NAME TYPE VALUE ------------------------------------ ----------- -------------------- optimizer_use_invisible_indexes boolean FALSE
To force the optimizer to use all invisible indexes at database level. then set it to TRUE.
SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;
System altered.
SQL> show parameter optimizer_use_invisible_indexes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean TRUE
SQL>
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 INVISIBLE
SQL> explain plan for select count(*) from dbaclass.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Plan hash value: 853747123
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| EMP_ID1 | 1 | 5 | 1 (0)| 00:00:01 | --- >>>>>> INDEX USED
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.
We can see the optimizer is using the INDEX, despite it is being invisible.
What is its use?
Most common use is while dropping the INDEX . If we are planning to drop index, then before dropping , keep the index in invisible mode and monitor for sometime. once things are good, we can drop it later.( Because post dropping, creating a index will take lot of time, if table size is huge).
