CREATING VIRTUAL INDEXES

posted Jan 28, 2012, 1:45 PM by Sachchida Ojha   [ updated Jan 28, 2012, 1:45 PM ]
Creating a virtual index is just what it sounds like; you create an index but it has no physical  existence! A virtual index is also referred to as a nosegment index or even a fake index, and you create it by specifying the "nosegment" clause when creating an index.

SQL> create index dbaref_emp_virtual_idx1 on employee(last_name) nosegment;

Index created.

SQL> select index_name,column_name,table_name from dba_ind_columns where index_name like 'dbaref_emp_virtual_idx1';

index_name                                 column_name                   table_name
--------------------                               -------------------                    ------------------
dbaref_emp_virtual_idx1                 last_name                       employee


Virtual index we just created does not take up any storage and is not available to cost optimizer by default. If you query the DBA_INDEXES view, you won't see the virtual indexes you have just created.

SQL> select index_name, table_name from dba_indexes where index_name like 'dbaref_emp_virtual_idx1';

no row selected.

YOU MAKE THE INDEX VISIBLE TO THE OPTIMIZER BY SETTING THE FOLLOWING UNDOCUMENTED INIT PARAMETER:

SQL> alter session set "_use_nosegment_indexes" = true;

session altered.

Setting the _use_nosegment_indexes parameter doesn't mean that the database will actually use the index; after all index doesn't really exist.

You can use this parameter to check if an execution plan for a query will use the index or not.

=====================================================================================
Even after creating a virtual index on a column, you can create a regular index on the same column.
=====================================================================================


Comments