Coskan’s Approach to Oracle

April 2, 2007

Using Virtual Indexes

Filed under: Basics, Tips — coskan @ 2:28 pm

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)
NOSEGMENT;

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);

code_listing_8

References Used:

Index Internals Presentation of Julian Dyke (www.juliandyke.com)

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_15.shtml

About these ads

1 Comment »

  1. The signals connect with concentrated nerve places, which in turn spread the signal
    to the complete abdominal location.

    Comment by Marsvenusatwork.Com — April 21, 2013 @ 11:04 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers

%d bloggers like this: