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
but ADDM in 10g does that work for us, doesnt it?
ReplyDeleteI read over your blog, and i found it inquisitive, you may find My Blog interesting. My blog is just about my day to day life, as a park ranger. So please Click Here To Read My Blog
ReplyDeletehttp://www.juicyfruiter.blogspot.com