Virtual Index in Oracle - Used to check feasibility before creating Index

Virtual Index :  Virtual index has no segment allocated. it means index data are not actually there in the DBA_SEGMENTS view. it allows the user to check the feasibility of the index before creating it. CBO will recognize existence of virtual index and create the explain plan. So, user can check the explain plan and determine to create the index or not.


Creating Virtual Index:

It can be achieved by using NOSEGMENT caluse with create index command. 
for example : 

Suppose, we want to create one index on table std_table. which have 5000 rows. 

create table  std_table(
stid  int,
stname  varchar2(10),
address varchar2(10)
);

begin
for i in 1 .. 5000
loop
insert into std_table values (i, 'id_'|| i, 'addr_'||i );
end loop;
end ;

Now if we query the table then we will get the result like below..

SQL> select  * from  std_table where  stid = 54;

      STID STNAME     ADDRESS
---------- ---------- ----------
        54 id_54      addr_54


Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    57 |    24   (0)|
|*  1 |  TABLE ACCESS FULL| STD_TABLE |     1 |    57 |    24   (0)|
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STID"=54)

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        475  bytes sent via SQL*Net to client
        337  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Now, Let's create the virtual index and execute the select query again.



SQL> CREATE INDEX IDX_STD ON STD_TABLE(STID) NOSEGMENT;

Index created.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SQL> select  * from  std_table where  stid  = 52;

      STID STNAME     ADDRESS
---------- ---------- ----------
        52 id_52      addr_52


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    57 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| STD_TABLE |     1 |    57 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_STD   |    20 |       |     1   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STID"=52)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        107  consistent gets
         34  physical reads
        692  redo size
        475  bytes sent via SQL*Net to client
        337  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


well you can see that newly created index "IDX_STD" is used in the explain plan. After that you can drop the index if it is not usable or you can drop this one and create new one.