Invisible Index in Oracle

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;

 

Advertisements

One thought on “Invisible Index in Oracle

  1. Pingback: Invisible Index in Oracle — Smart way of Technology – Tech Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.