Database version:- oracle 11.2.0
Invisible Indexes, which are basically indexes that exist and are maintained by Oracle but are "invisible" to the Cost based optimizer(CBO). Specific sessions can be set to see these
invisible indexes as necessary.The invisible index is an alternative of dropping or making an index unusable. This feature is also functional when certain modules of an application
require a specific index without affecting the rest of the application.
First, create a simple table and associated index.
SQL> conn soumya/soumya
SQL> set autotrace on
SQL> set linesize 200
SQL> create table soumya_test (Rollno number(5),name varchar2(20));
Table created.
SQL> create index soumya_test_ind on soumya_test(Rollno);
Index created.
Now insert data into the table.
SQL> begin
2 for x in 1..1000 loop
3 insert into soumya_test ( Rollno , name)
4 values ( x , default);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
By default, indexes are created as "VISIBLE" .
SQL> SELECT table_name,index_name, visibility FROM user_indexes WHERE index_name = 'SOUMYA_TEST_IND';
TABLE_NAME INDEX_NAME VISIBILITY
------------------------------ ------------------------------ ----------
SOUMYA_TEST SOUMYA_TEST_IND VISIBLE
SQL>
As the index is visible, it can be considered and used by the CBO .
Here we checked our indexed column by user_ind_columns.
We are now going to check how the query will process by the oracle server.
SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected.
Now alter the visibility of index SOUMYA_TEST_IND.
SQL> alter index SOUMYA_TEST_IND invisible;
Index altered.
Check the index status.
SQL> select index_name,visibility from user_indexes
where table_name='SOUMYA_TEST';
INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE
As, we can see that index is invisible now oracle wont use it.
now we again check the same explain plan for same query and compare the results.
SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
In above explain plan we see oracle didn't use the index like it did in previous query.
Now change back the visibility once again.
SQL> alter index SOUMYA_TEST_IND visible;
Index altered.
any specific sessions can be altered so they can "see" these invisible indexes.
SQL> select index_name,visibility from user_indexes
2 where table_name='SOUMYA_TEST';
INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE
SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
We see that optimizer is still not using the invisilbe index so we need to query the explain plan again.
SQL> explain plan for
select * from soumya_test where Rollno in (555,726,959);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected.
Now we see the invisible index is being used.
