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.

122 thoughts on “Outline不能正确使用的问题

  1. We just need to share it with you which I am inexperienced to putting up a blog and very much cherished your report. Likely I am most likely to save your article post . You absolutely have wonderful article reports. Be Grateful For it for expressing with us your domain write-up.

  2. I notice there are many trash on this blog page. Are you gonna be planning to clear it up. It’s Possible That I can certainly help to to implement?

  3. A person necessarily assist to make critically posts I might state.

    This is the very firwt time I frequented your website page and uup to now?
    I amazed with the researc you made to create this particular submit incredible.
    Wonderful job!

  4. I will immediately snatch your rss as I can not find your e-mail subscription hyperlink or newsletter service. Do you’ve any? Please permit me recognise in order that I may just subscribe. Thanks.

  5. Simply desire to say your article is as astounding. The clarity in your post is just great and i can assume you’re an expert on this subject. Fine with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please continue the gratifying work.

  6. It is perfect time to make some plans for the future and it’s time to be happy. I have read this post and if I could I want to suggest you few interesting things or tips. Maybe you can write next articles referring to this article. I desire to read even more things about it!

  7. Great weblog right here! Additionally your web site a lot up fast! What host are you the usage of? Can I get your associate link in your host? I desire my site loaded up as fast as yours lol

  8. Good article and right to the point. I don’t know if this is really the best place to ask but do you folks have any thoughts on where to employ some professional writers? Thanks in advance :)

  9. Hi, anyone ever hear about that new electrician on Chambers Street by the name of New York Electrician, 94 Chambers St, New York, NY 10007 (646) 415-6723

  10. Hey, you used to write fantastic, but the last several posts have been kinda boring¡K I miss your tremendous writings. Past several posts are just a little out of track! come on!

  11. I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all web owners and bloggers made good content as you did, the internet will be much more useful than ever before.

  12. Hiya, I am really glad I have found this info. Nowadays bloggers publish just about gossips and internet and this is actually irritating. A good site with interesting content, this is what I need. Thank you for keeping this web site, I’ll be visiting it. Do you do newsletters? Can not find it.

  13. You actually make it appear really easy together with your presentation however I find this topic to be really one thing which I think I’d never understand. It seems too complex and extremely vast for me. I am having a look forward for your next put up, I’ll try to get the dangle of it!

  14. My spouse and I stumbled over here by a different website and thought I might as well check things out. I like what I see so now i am following you. Look forward to checking out your web page yet again.

  15. Do you have a spam problem on this website; I
    also am a blogger, and I was wondering your situation; many of us
    have created some nice procedures and we are looking to
    exchange techniques with other folks, please shoot me an e-mail if interested.

  16. whoah this blog is fantastic i love studying your posts.
    Keep up the great work! You recognize, lots of people
    are looking around for this info, you could aid them greatly.

    Here is my weblog – permanent weight loss (Dexter)

  17. hello!,I like your writing very much! share we communicate more approximately your article on AOL? I require a specialist on this space to unravel my problem. Maybe that is you! Having a look ahead to peer you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>