How to use Virtual Index in Oracle



Last updated on September 21st, 2017 at 05:23 pm

What is  Virtual Index in Oracle ?

a) A virtual index is a “fake” index whose definition exists in the data dictionary, but has no associated index segment.

b) Many time 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.

c) We can analyze virtual indexes.

d) You cannot rebuild a virtual index; it throws an ORA-8114: “User attempted to alter a fake index”

 

e) 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 nosegment 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’;

OWNER OBJECT_NAME


SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


CREATED LAST_DDL_ TIMESTAMP STATUS T G S


APPS FND_PROFILE
887 PACKAGE
30-AUG-05 30-AUG-05 2016-04-18:00:00:00 VALID N N N

 

APPS FND_PROFILE
888 PACKAGE BODY
30-AUG-05 30-AUG-05 2016-08-30:13:51:16 VALID N N N

 

3) Check the 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’;

OBJECT_NAME OBJECT_TYPE


TEST_INDEX_V INDEX

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 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).


Leave a Reply