How to Use Virtual Indexes in Oracle Database?

A virtual index is a “fake” index whose definition exists in the data dictionary, but has no associated index segment. 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. If we observe that optimizer is creating a plan which is expensive and SQL tuning advisor suggest us to create an index on a column, in case of production database it may not be always feasible to create an index and test the changes. We need to make sure that the created index will not have any negative impact on the execution plan of other queries running in the database.

Lets test it 😊

CREATE A TEST TABLE

SQL> select count(1) from dba_objects;

COUNT(1)
----------
52220

SQL> create sequence sys.sequencetest cache 10000;
Sequence created.
 
SQL> alter session enable parallel dml;
Session altered.

SQL> alter session enable parallel ddl;
Session altered.

SQL> set timing on;
SQL>
SQL> create /*+ parallel */ table sys.test as
  2    select sequencetest.nextval ID, d.*
  3    from dba_objects d, (select 1 from dba_objects where rownum < 500)r;
Table created.
Elapsed: 00:00:57.39
SQL>

SQL> select count(1) from sys.test;

COUNT(1)
----------
26057281

SQL> select ds.owner, ds.segment_name, ds.bytes/1024/1024/1024 size_gb from dba_segments ds where ds.segment_name='TEST';

OWNER		        SEGMENT_NAME		        SIZE_GB
--------------------	--------------------		----------
SYS 			TEST				3.84020996

SELECT ANY VALUE FROM THE TABLE

SQL> select * from sys.test where object_name = 'STANDARD';

CHECK THE EXPLAIN PLAN FOR THE SELECT QUERY

SQL> set autotrace traceonly explain
SQL>
SQL> select * from sys.test where object_name = 'STANDARD';

CREATE A VIRTUAL INDEX ON THE TABLE CREATED

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.

SQL> create index test_index on sys.test (object_name) NOSEGMENT;
Index created.
SQL>

GENERATE SOME STATISTICS OF YOUR VIRTUAL INDEXES

SQL> exec dbms_stats.generate_stats('SYS', 'TEST_INDEX');

CHECK DBA_INDEXES AND DBA_OBJECTS

So, The object exists in database, but we dont have segment for the same.

SQL> set autotrace off
SQL>
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX';
no rows selected
SQL>
SQL> col OBJECT_NAME format a20;
SQL>
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME             OBJECT_TYPE
-------------------- 	-----------------------
TEST_INDEX           	INDEX

CHECK AGAIN THE EXPLAIN PLAN FOR THE SELECT QUERY

We can clearly observe that the index is not being used.

SQL> set autotrace traceonly explain
SQL>
SQL> select * from sys.test where object_name = 'STANDARD';

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

CHECK AGAIN THE EXPLAIN PLAN FOR THE SELECT QUERY

The cost of the query has decreased to 5 from 72076.

This saves so much time. After checking your execution plans, if you are ok with them you can actually create the index.

To create this virtual index as a real one, you must drop it and re-create it without NOSEGMENT clause.

SQL> select * from sys.test where object_name = 'STANDARD';

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)

Notes

• It can be used only in Enterprise Edition not Standard Edition. Standard Edition has no deferred segment creation so you cannot create a data object without data segment.
  
• You 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>;

DROP TEST TABLE & INDEX & SEQUENCE

SQL> drop table sys.test cascade constraints;
Table dropped.

SQL> drop sequence sequencetest;
Sequence dropped.