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

Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.