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!
One problem with the
INDEX(tab1 ind1)
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
INDEX(tab1 tab1(ind_col1, ind_col2))
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.
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.
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 that I’ve ordered. Do yourself a favour and get a copy!
Thought this was a great presentation – full of gems like this one!
I guess not all of us are as lucky as Pete Finnegan – winner of the draw for Jonathan’s new book in that very presentation!
Yeah, there were so many things that I could have picked out but I thought that one would be useful to everyone. For much more detail, get Jonathan’s book – I’m waiting for Amazon to deliver my copy.
Jonathan’s presentations are also available online.