{"id":911,"date":"2005-11-06T12:00:00","date_gmt":"2005-11-06T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=911"},"modified":"2005-11-06T12:00:00","modified_gmt":"2005-11-06T12:00:00","slug":"a-better-index-hint","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/11\/06\/a-better-index-hint\/","title":{"rendered":"A better INDEX hint"},"content":{"rendered":"

One of my old Sun colleagues, Jari Kuhanen, mentioned to me that he hoped to see more tips from the UKOUG conference appearing on blogs. Sadly, the few notes I made might as well be in Greek for all the sense they make to me when I read them, but hopefully I’ve got this one right from Jonathan Lewis’ Hints presentation. Apologies if it’s old news to you! <\/p>\n

One problem with the<\/span><\/p>\n

INDEX(tab1 ind1)<\/pre>\n
hint that will direct the cost-based optimiser to use the ind1 index on tab1 is that, should the index be dropped and renamed for some reason, the hint won’t work any more. In 10g, there is an improved version of this hint<\/p>\n
INDEX(tab1 tab1(ind_col1, ind_col2))<\/pre>\n
By specifying the index columns rather than the index name, Oracle can pick up any index with these columns as the leading portion of the index key. When you think about it it’s a much better hint, approaching the problem from the right direction – the search criteria from the logical perspective rather than a particular physical object.<\/p>\n
Jonathan added to a previous blog with a comment, so I’ll drop him a line and I’m sure he’ll correct me here if my memory is flawed.<\/p>\n
Jonathan’s CBO presentations at the UKOUG conference were terrific, regardless of your experience level, and I’m expecting great things from his new book<\/a> that I’ve ordered. Do yourself a favour and get a copy!<\/p>\n","protected":false},"excerpt":{"rendered":"
One of my old Sun colleagues, Jari Kuhanen, mentioned to me that he hoped to see more tips from the UKOUG conference appearing on blogs. Sadly, the few notes I made might as well be in Greek for all the sense they make to me when I read them, but hopefully I’ve got this one… Continue reading A better INDEX hint<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-911","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1333,"url":"http:\/\/orcldoug.com\/blog\/2007\/10\/29\/parallel-tidbits\/","url_meta":{"origin":911,"position":0},"title":"Parallel Tidbits","date":"October 29, 2007","format":false,"excerpt":"A couple of small things I noticed recently. The first was a mention of the presence of Bloom Filters in PX plans over on Greg Rahn's blog, another one that I picked up through OraNA which looks very interesting. Greg's looking for ideas for performance related blogs at the moment,\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":912,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/06\/bloghi-com\/","url_meta":{"origin":911,"position":1},"title":"bloghi.com","date":"November 6, 2005","format":false,"excerpt":"I'm a bit disappointed. I was very impressed that Howard Rogers' blog emailed me whenever someone commented on a blog that I had commented on earlier. I thought this was the result of some Dizwell magic so I emailed Howard to find out how it was done, but it turns\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1611,"url":"http:\/\/orcldoug.com\/blog\/2010\/09\/05\/swing\/","url_meta":{"origin":911,"position":2},"title":"Swing!","date":"September 5, 2010","format":false,"excerpt":"Wow - almost 4 months since my last post that could be described as vaguely technical. It's amazing how time flies when you're having fun. (Although there may be a hint of irony in that statement.)Work has been insane for various reasons and personal life has been unusually involved too\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1006,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/27\/whats-going-on-out-there\/","url_meta":{"origin":911,"position":3},"title":"What’s going on out there","date":"June 27, 2006","format":false,"excerpt":"Eventually, you start to run out of titles to say 'here are a few links I've been looking at this week' \ud83d\ude09I enjoyed the debate about the value and dangers of the Internet over on Tom Kyte's blog. There are aspects of both sides of the argument I agree with\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1642,"url":"http:\/\/orcldoug.com\/blog\/2011\/08\/10\/real-time-sql-monitoring-statement-not-appearing\/","url_meta":{"origin":911,"position":4},"title":"Real-Time SQL Monitoring – Statement Not Appearing","date":"August 10, 2011","format":false,"excerpt":"Like Greg Rahn, I've looked at many SQL Monitoring reports over the past year or two. Possibly not as many as Greg, but it's become my default method of communicating* SQL performance issues to colleagues to the point that some might be finding it irritating by now, whilst others are\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1536,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/16\/oow-2009-going-home\/","url_meta":{"origin":911,"position":5},"title":"OOW 2009 – Going Home","date":"October 16, 2009","format":false,"excerpt":"So the time has arrived. We're just waiting for our car to take us to the airport and it's time to say thanks to ....- Friends, Oak Table Network Members, and fellow ACEs (some qualify in all three categories) who are the essence of attending the conference for me. I\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/packed.jpg?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/911","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=911"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/911\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=911"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}