Databases Reference
In-Depth Information
Since all the requisite data is found within the new composite index, the database doesn't have to
perform the additional table scan.
Creating Virtual Indexes
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, as shown here:
SQL> create index fake_idx on employees(last_name) nosegment;
Index created.
SQL>
You can issue the following query to confirm that the index is present:
SQL> select index_name,column_name,table_name from dba_ind_columns
2* where index_name like 'FAKE%';
INDEX_NAME COLUMN_NAME TABLE_NAME
------------ ------------------ ------------
FAKE_IDX LAST_NAME EMPLOYEES
SQL>
The virtual index that you've created doesn't take up any storage—and isn't available to the cost
optimizer by default. If you query the DBA_INDEXES view, you won't see the fake indexes you've
created, as shown here:
SQL> select index_name,table_name from dba_indexes
2* where index_name like 'FAKE%'
SQL> /
no rows selected
SQL>
The reason this query returns no rows is that the DBA_INDEXES view shows information only about
actual index segments, and since a fake index doesn't really use storage, it doesn't show up in this view.
You make the index visible to the optimizer by setting the following undocumented initialization
parameter:
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered.
SQL>
