Virtual Index, A New Learning….

I always say that no one is perfect when it comes to a tech discussion. There is always a very fare chance that you may not be knowing something and/or have not seen/heard about it before. Almost everyday , you can learn some thing new. The same happened with me too today. While discussing about Indexes,what they are,how they work, one delegate pointed out that there  is a concept of “Virtual Indexes” in oracle from 9i onwards. As the name suggests, these indexes basically are used to test  that whether the existance of an index can be good/bad/of no effective or a column. Though these are actually created by a create command, they do not store  any data as such and just contain statistics. So if we get a recommendation from Sql Access Advisor tool of 10g, that we should be having an index on a column of our table, we can use these indexes to test that whether this index will work  for us or not? ? Sounds cool, right! Yes, its indeed is but  the only sad part was , I didn’t know about this before :-(. So when was told, I started to look for the information about these indexes and I found this,

http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2007-05-31.6959101573

An excellent note by Chris Foot, explaining in gory detail about the indexes. This post had a demo page too linked with it, so I am not going to repeat the same demos again here. You may want to look at the demo page,which is mentioned in the post itself. I shall just mention one thing, which is for my reference, you can create virtual indexes by  adding a keyword, ” NOSEGMENT” to the Create Index command like this,

sql> create index tidx on test(empno) nosegment;

In order to use these indexes, you need to set an undocumented parameter, “_use_nosegment_indexes”=true like this,

sql> alter session set set “_use_nosegment_indexes”=true;

Surely enough,it was a new and informative learning at least for me as I didn’t know about these virtual indexes before. Please refer to the above mentioned blog post to read more about them. Once again, learned something new, great!