How to create and alter the invisible Index in Oracle

Invisible index is used in Oracle to test the functionality of index that its help in increasing performance.It is an alternative way to check application behavior with index or without index. It’s released in 11g

You can test the index by creating it with invisible mode and test it on on SQL statements.

1. Suppose we have employee table having 50000 records,create one index

create index index_name on employeee(emp_id ) Invisible;

2. Check the execution plan

SET AUTOTRACE ON
SELECT * FROM employee WHERE id = 34567;

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

3. Test the index used in upper query need to alter the session:

alter session set optimizer_user_invisible_indexes = TRUE;

4. Run the Query again:

select * from employee where id = 34567;

-----------------------------------------------------------------
| Id| Operation        | Name   |Rows|Bytes|Cost(%CPU)|  Time   |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT |        |1   | 3   |1 (0)     |00:00:01 |
|*1 | INDEX RANGE SCAN | emp_IDX|1   | 3   |1 (0)     |00:00:01 |

5. Check index visibility

select index_name,visibility from user_indexes where table_name='employee';
Index_name  Visibility
----------- ------------
emp_idx       Invisible

6. you can also convert invisible index to visible by following command:

alter index emp_idx visible;

7. Same as invisible by following commands:

alter index emp_idx invisible;