Always wanted this – the ability to create an index on production without impacting the queries being fired by application but at the same time test the impact an index creation can cause. Invisible indexes are useful alternative to making an index unusable or to drop it.
The optimizer ignores the index that are marked “Invisible” unless you set the initialization parameter “OPTIMIZE_USE_INVISIBLE_INDEXES” to TRUE. This parameter can be set both at a session level as well as system level.
Usage of Invisible Indexes
One can use invisible index for testing the impact of removing an index. Instead of dropping the index we can make it invisible and its effect.
One can speed up operations by creating invisible indexes for infrequent scenarios. Invisible index will make sure that the overall performance of the application is not affected.
Gives you the flexibility to have both b-tree (to guarantee unique PK) as well as bitmap indexes (on FK columns) in a data warehouse application.
How to create?
Multiple options – either mention “INVISIBLE” clause at the time of index creation or use ALTER command to make an index “INVISIBLE”.
CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
ALTER INDEX index INVISIBLE;
To make the Index “VISIBLE”
ALTER INDEX index VISIBLE;
A new column “VISIBILITY” is available in *_INDEXES data dictionary views to know if an index is visible or invisible.
Example
SQL> create index indx_job on emp1(job);Index created.
SQL> explain plan for select * from emp1 where job='CLERK';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 4 | 348 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_JOB | 4 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> explain plan for select * from emp1 where job='CLERK';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2226897347
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 | 4 | 348 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
Session altered.
SQL> select index_name,visibility from user_indexes where table_name='EMP1';
INDEX_NAME VISIBILIT
------------------------------ ---------
INDX_JOB INVISIBLE
SQL> explain plan for select * from emp1 where job='CLERK';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 4 | 348 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_JOB | 4 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
