Virtual Indexes
                                                 Last update (2008-03-02 01:08:19)
                                                                                                                   Date added (2008-03-02 00:59:54)

Summary
From version 8.1.7.4 and after, there is the concept of Fake indexes or Virtual indexes.

You can create a virtual index to check possible performance gains. Since there is no storage area associated with these indexes, they do not have any other overhead. Virtual indexes is to simulate the existence of an index - without actually building a full index.

Fake Indexes Characteristics:
1. The NOSEGMENT clause is required for a Fake/Virtal Index to be created. Example:
      CREATE INDEX idx2 on myobj (object_name) NOSEGMENT;

2. These are permanent indexes and exist in the database without 
    using any storage space (no segments are allocated to these indexes). 
    They have to be explicitly dropped if they are not needed.

3. The hidden initialization parameter "_use_nosegment_indexes" 
    needs to be set to TRUE in order to enable the CBO to use these indexes.
    ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

4. These indexes are reflected in DBA_OBJECTS but are not reflected in DBA_INDEXES.

5. The following query can be used to identify Fake Indexes:
      SELECT  o.object_name AS fake_index_name FROM dba_objects o
      WHERE   o.object_type = 'INDEX' AND  NOT EXISTS
      (SELECT null FROM dba_indexes i WHERE o.object_name = i.index_name AND o.owner = i.owner);

6. Trying to run an ALTER INDEX command on a fake index results in the error ORA-8114.

7. It is possible to ANALYZE these Indexes.  However no corresponding statistics are populated.
Reviews
Categories
Filters
Search