What is Virtual Index in Oracle ?
- A virtual index is a “fake” index whose definition exists in the data dictionary, but has no associated index segment.
- Many time sql tuning advisor recommends you creating new index and you want to test the new index. In this case It can take good amount of time to add indexes to large tables and it will consume large disk space also if the table is big.Also the additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be specially problematic when you are trying to identify problems on a production system. Virtual indexes solves this problem
- The purpose of virtual indexes is to simulate the existence of an index – without actually building a full index
- This allows developers to run an explain plan as if the index is present without waiting for the index creation to complete and without using additional disk space.
- We can analyze virtual indexes.
- You cannot rebuild a virtual index; it throws an ORA-8114: “User attempted to alter a fake index”
- You can drop the index just as a normal index.
SQL> drop index <index_name>;
Important points to Remember
(1) We need to set “_USE_NOSEGMENT_INDEXES” to true at session level to use this feature
(2) Virtual indexes are created with the addition of no-segment portion at the end of index creation script
Example to demonstrate the use of Virtual Index in Oracle
(1) Create a sample table, say virtual_test_t
SQL> create table virtual_test_t as select * from dba_objects where rownum < 100000;
(2) Select any value from the table
SQL> select * from virtual_test_t where object_name = 'FND_PROFILE';
(3) Check the oracle Explain plan for the SELECT query.
SQL> set autotrace traceonly explain
SQL> select * from virtual_test_t where object_name = 'FND_PROFILE';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 1416 | 156 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| VIRTUAL_TEST_T | 8 | 1416 | 156 (2)| 00:00:02 |
(4) Create a virtual index on the table created.
SQL> create index test_index_v on virtual_test_t(object_name) nosegment;
Remember, in order to create a virtual index you need to specify the NOSEGMENT clause in the CREATE INDEX statement.
Also note by executing the above statement, an index segment is not created.
(5) You can check the same with the following:
SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = 'VIRTUAL_TEST_T' and index_name = 'TEST_INDEX_V';
no rows selected
SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX_V';
So, The object exists in database, but we dont have segment for the same.
(6) Now run the same to check if the index is being used.
SQL> set autotrace traceonly explain
SQL> select * from virtual_test_t where object_name = 'FND_PROFILE';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 1416 | 156 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| VIRTUAL_TEST_T | 8 | 1416 | 156 (2)| 00:00:02 |
We can clearly observe that the index is not being used.
(7) To make use of the virtual index created, we need to set _USE_NOSEGMENT_INDEXES parameter to true.
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;
Session altered.
(8) Now, run the same SELECT statement.
SQL> select * from virtual_test_t where object_name = 'FND_PROFILE';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| VIRTUAL_TEST_T | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_INDEX_V | 216 | | 1 (0)| 00:00:01 |
Once you set this hidden parameter, the oracle optimizer will start using the virtual index you created on this table.
If you run this query from any other session, it will not use this virtual index (as we have used “alter session” statement).