Virtual Index in Oracle Database

Virtual index is definition of index which is physically not created that is segment is not created for such index. The main purpose of virtual index is to determine whether the created index would be useful or not for the SQL statement.

How to create Virtual Index in Oracle?

The NOSEGMENT clause with CREATE INDEX statement specifies that the index is virtual that is it is not associated with any physical segment.

How to use virtual index ?

The _USE_NO_SEGMENT parameter tells oracle to use the virtual indexes in explain plans if optimizer determines that such index would be useful.

Below example shows how to use virtual index. I am generating execution plan for statement.

SQL> explain plan for
  2  select * from test_tab_1
  3  where object_id=1000;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Plan hash value: 3316545227

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |   116 | 26733   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB_1 |     1 |   116 | 26733   (1)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1000)

13 rows selected.

The above execution plan shows that the table TEST_TAB_1 is going for Full Table Scan. I am thinking to create an index on OBJECT_ID column. Now question is that the created index would be useful or not. To check this first I am going to create Virtual Index.

SQL> create index idx_object_id on test_tab_1(object_id) nosegment;

Index created.

Now to use this index I am changing the value for _use_nosegment_indexes parameter to true at session level and again checking the EXPLAIN PLAN for the statement.

SQL>
SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL>
SQL> explain plan for
  2  select * from test_tab_1
  3  where object_id=1000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Plan hash value: 4053964975

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |   116 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB_1    |     1 |   116 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=1000)

14 rows selected.

SQL>

The above plan shows that the index would be useful for this statement so that I can take call to create this index. Post your verification don’t forget to drop virtual index as sometimes it may create some problems.

Virtual Index in Oracle Database
Tagged on:     

4 thoughts on “Virtual Index in Oracle Database

  • September 18, 2015 at 5:11 am
    Permalink

    Nice!!! Learn some thing new today . Thanks man

    Reply
  • November 10, 2015 at 1:01 pm
    Permalink

    Yes really new learning. Thanks for post.

    Reply

Leave a Reply