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,
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!
New learning for me too. 🙂
Keep it up.
Thanks mate! Yes, it was a new learning for me. We had 2 great sessions and delegates were really great! Besides virtual indexes, we did spend some time playing around with external procedures and worked on one feature that we wanted. I shall blog about that in an upcoming post.
Well i was trying out this Virtual Index stuff.It now seems to be more interesting to me because when i create a proper index, it is not being used, and the explain plan shows full table scan but when i create the virtual index it is being used as per the explain plan trace.I am trying out with the same example given by chrisfoot.
Seems like the real game begins now.
Yes I did find the same. To be honest, I didn’t try to drill down the issue as I am struck in some other stuff. I guess, there must be some thing trivial related to Virtual Indexes which causes this weird behavior. I may be wrong too.
Do share if you find some thing interesting.
Well what are the areas i should look into.I generated a 10046 trace of the session which shows *FULL TABLE ACCESS* when using the virtual index as obvious, as the index is fake/virtual.
Yes makes sense, I just noticed that you used autotrace to show the plan which is a wrong way to get the plan actually. You should have used tracing in the first place only, normal trace too would have shown the correct plan.