Jonathan has a very good post on the oracle-l list on virtual indexes. alsthough i've known about this for wa while never had a chance to play around with them.
this post is a goos expalnation with use pro and cons .
-----------------------------------
In the context of "Tales of the Oak Table", the virtual indexes were, indeed, access paths intothe x$ fixed tables. They can be seen in thevirtual table v$indexed_fixed_columns, whichhas the structure:
Name Null? Type
----------------------- -------- ------------
TABLE_NAME VARCHAR2(30)
INDEX_NUMBER NUMBER
COLUMN_NAME VARCHAR2(30)
COLUMN_POSITION NUMBER
These exist and are predefined.
The Virtual Index Wizard probably relates to the"nosegment" feature, whereby you can do the following:
create index t1_noseg on t1(small_vc) nosegment; alter session set "_use_nosegment_indexes"=true;
From this point on, your session can use EXPLAIN PLANto see if the optimizer would use this index if it existed, butyou don't have to build it, and no-one else can see it. On the plus side, it gives you some idea about whether a newindex is worth building; on the minus - the statistics are inferredfrom the column statistics, so there is no guarantee that the indexwould be used if you really created it.
Regards
Jonathan Lewis
