While looking at Mr Julian Dyke’s presentation about index internals I saw a new (at least new for me) feature Virtual Indexes. It is there since the 8.1.5 release but i was unaware 😦 .
These indexes are not physically located on a segment but they have a data dictionary definition (you can’t add a real index with same name of virtual index). Virtual indexes allow user to test a potential advantages or disadvantages of a new index prior to actually building the new index in the database . For example you want an index on a table with 10 million rows and you do not sure about using an index then you can use virtual indexes for having idea about new execution plans.
CBO do not use virtual indexes by default without setting the undocumented parameter “_use_nosegment_indexes” to true and this is something you must consider before deciding to use virtual indexes. Since it is undocumented then it is not supported, unless the Oracle say that you can set. In my tests with virtual index i got similar cost results and same access paths from CBO comparing to real index usage.
=>Index Creation Syntax (you add nosegment clause to instead of storage clauses)
CREATE INDEX INDEX_NAME
ON TABLE_NAME (INDEX_COLUMN)
You have to generate statistics for the virtual index to get realistic costs. Creation of the index is not enough, it is called virtual but it acts like if it is real so if you want a good index simulation don’t forget the statistics. You can use the syntax below to generate statistics;
SQL>EXECUTE DBMS_STATS.GENERATE_STATS (user,’index_name’)
Don’t forget to set the session wide “_use_nosegment_indexes” parameter .
SQL> ALTER SESSION SET “_use_nosegment_indexes” = TRUE;
=>Sample Demonstration (Tests are done on 10.2.0.1 XE);
Index Internals Presentation of Julian Dyke (www.juliandyke.com)