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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
