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;
Pingback: Invisible Index in Oracle — Smart way of Technology – Tech Stuff