Recently I came across term called Virtual Index or some call it fake indexes. I will use term Virtual Index to refer these indexes.

This type of indexes has been extensively used by most Oracle Tuning Tools in the market. You can say almost all the tools which promise to give “if then analyzes” on execution plan, must be using this capability.

In simple words it allow us to simulate the existence of an index and test its impact without actually building a actual index. This saves lot of time and most important I/O from and to Oracle resources (Disk, CPU, etc).

Once the Virtual Index is created, we can run an explain plan as if the index is present. Once we have decided to choose what indexes to create, we can proceed to creation of actual indexes.

Most important thing to note here is that it important we drop virtual indexs after we have done our analysis and tuning exercise.

Following is what we need to do first for our “if then analysis” through SQL*Plus

SQL> set autotrace on explain;

SQL> alter session set “_use_nosegment_indexes” = true;

Session altered.

Now I have following table called “BALANCES”

SQL> DESC BALANCES;
Name Null? Type
—————————————– ——– —————————-
BALANCE_DATE DATE
BALANCE_AMOUNT NUMBER(9,2)

Before creating virtual index, explain plan out put was as follows:

Execution Plan
————————————–
Plan hash value: 4006579748

————————————–
| Id | Operation | Name |
————————————–
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| BALANCES |
————————————–

Now I created the virtual index as follows, syntax is almost same as normal index but check out the last word “NOSEGMENT”. This is what tells oracle what we intent to do.

SQL> CREATE INDEX balance_idx ON balances(balance_date) nosegment;

Index created.

After creating it, explain plan output was as follows:

Execution Plan
—————————————————
Plan hash value: 842018266

——————————————————
| Id | Operation | Name |
——————————————————
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| BALANCES |
|* 2 | INDEX RANGE SCAN | BALANCE_IDX |
——————————————————

Now Don’t forget to drop the index:

SQL> DROP INDEX balance_idx;

Index dropped.