Outline不能正确使用的问题
今天同事遇到一个outline不能正确使用的问题问我。抽象描述类似于下面的问题
create outline ol3732308613_tmp for category GOOD_PLAN on
select /*+ index(a,a_idx) */ … from a;
生成outline后,explain plan select /*+ index(a,a_idx) */ … from a; 却发现没有走a_idx,而是选择了full table scan
我看了一下ol$hints表,发现其并没有使用index hint,而是使用了INDEX_RS_ASC,并且还有一个hint为OPTIMIZER_FEATURES_ENABLE(’9.2.0′)
数据库版本为10g,optimizer_features_enable为9.2.0。这样的话INDEX_RS_ASC这个hint不能被识别,因为其为10g所引入的。
在创建outline前加上alter session set optimizer_features_enable=’10.2.0.3′ 就可以了
这样OPTIMIZER_FEATURES_ENABLE(’9.2.0′) 被替换为OPTIMIZER_FEATURES_ENABLE(’10.2.0.3′)。INDEX_RS_ASC 可以被正确使用。
about INDEX_RS_ASC (from jonathanlewis’s blog)
Bonus: The index() hint tell Oracle to use an index, but does not specify whether that the usage should be a full scan or a range scan. Because of some changes in costing in 10g, there have been cases reported where the optimizer – after hinting – picked a full scan when a range scan would have been a more intelligent choice. Consequently Oracle Corp. has introduced three new hints in 10.2.0.3 to “disambiguate” the index use. These are: index_rs(), index_rs_asc(), index_rs_desc(), which make explicit demands for an index range scan. It’s probably a good idea to use this option in all future code.
index_rs ?
good improvement
I have learn several just right stuff here. Certainly value bookmarking for revisiting. I wonder how so much attempt you set to make this kind of magnificent informative website.
thank you for sharing it! your posts are really nice … keep it up and come see my blog
+ + Slevin
Does your site have a contact page? I’m having problems locating it but, I’d like to shoot you an email. I’ve got some suggestions for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it improve over time.
hey all, I used to be simply checkin’ out this weblog and I really admire the basis of the article, and have nothing to do, so if anyone would like to to have an engrossing convo about it, please contact me on AIM, my title is heather smith
I would like to thank you for the efforts you’ve put in writing this web site. I’m hoping the same high-grade blog post from you in the upcoming also. Actually your creative writing abilities has inspired me to get my own web site now. Actually the blogging is spreading its wings fast. Your write up is a good example of it.
Outstanding post, you have pointed out some good points, I as well believe this is a very wonderful website.
I believe other internet site owners should really consider this amazing web-site as an example. Extraordinarily clean and intuitive design, in addition to outstanding content material! You are very skilled within this particular niche
Hahahahahahaha, this politics related YouTube video is really so comical, I liked it. Thanks in favor of sharing this.
you are really a just right webmaster. The website loading speed is incredible. It seems that you’re doing any distinctive trick. In addition, The contents are masterwork. you have performed a magnificent task in this topic!
I’ve been absent for some time, but now I remember why I used to love this website. Thanks, I’ll try and check back more frequently. How frequently you update your web site?
The popular Zune cell phone browser is certainly interestingly fantastic, however it is not as effective as all the iPod’s. The system functions certainly, yet isn’t as quickly for the reason that Chrome, and contains a good clunkier program. Any time you from time to time work toward using the web browser that is not a difficulty, however if you are intending to help you look at online a great deal from your very own PMP the iPod’s even larger filter and much better phone could be significant.
I really feel other sorts of site operators will want to consider this website as a model. Very clean and easy to use style and design, not to mention impressive subject material! You’re a guru when it comes to this valuable subject
I went over this site and I think you have a lot of superb information, bookmarked (:.
oh… and one more thought on the “protein sparing” concept with more carbohydrates if i may… I think the same could be said for fats, as in if one is “ketone adapted” and burns fat efficiently, that too could be “protein sparing”. I have not personally had good results with using fat as primary fuel source, but some on this forum seem to have had great success.
Very interesting details you have observed, thankyou for putting up.