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