Understanding how this works is really important
Harrison, June 06, 2001 - 6:47 pm UTC
Understanding what a tool is doing is a lot better than
just seeing the numbers, as this example points out.
Almost every tool has some limitation, and knowing what
they are for the tools we use is important; knowing a few
tools well would seem to be better than getting tricked
by this kind of stuff.
Does the Optimizer consider every access option for every object for a Query Plan?
Pramod, June 07, 2001 - 10:45 am UTC
Okay your point is that by hinting for the optimizer to do a full table scan we changed the queries cost and the plan. I have used hints extensively and I have added hints that push the cost much higher(typically does not make the cost less) and then run the query which actually runs faster. In effect when you give the optimizer a hint you are telling it you know better how to access the paticular object(Maybe because the statisics collect for the objects are old or not detailed enough). I always have assumed that any hint that I give the optimizer is going to cause the calculated resource cost for the query to be higher then the resource cost the optimizer calculated for the query with no hints. I base my assumptions on how the cost based optimizer finds a plan for a query which as I understand it is on the Lowest Total Cost for the query. I assumed it calculates the lowest cost by using the following simplified algorythm :
1)iterating throught the objects in the query
2)try different methods of access.
3)for each iteration calculate a resource cost based on avaiable statistics for the objects in the catalog
4)once it completes all possible iterations(statisitcal permutations)choose the plan with the lowest cost.
Is my understanding of how the cost based optimzer makes its choice of a queries plan incorrect?
Thanks for the Futher Clarification..
Pramod, June 08, 2001 - 8:37 am UTC
Tom,
Thank you very much for the further clarification on the cost based optimizers interworkings with hints.. I completly understand your point now... BTW I will be purchasing your book from Amazon today... They are still listing at pre-order status can't wait to get my copy...
Index hint
Jonathan Lewis, May 04, 2010 - 5:13 pm UTC
Tom,
I know this it 9 years late - but the follwoing comment has been quoted a couple of times on the OTN database forum and it's wrong:
"Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods"
An index hint tells the optimize that it must use an index (it it's legal) at a certain point in the decision tree - one of the indexes specified in the hint if any ones are specified, or any of the existing indexes; the arithmetic that produces costs is not affected by such hints.
May 06, 2010 - 1:46 pm UTC
Jonathan
thank you - you are absolutely correct, I mispoke on that.
I have examples in other places where I use the hint to show what the cost of using the index (or whatever) would be to show that the cost of using that other approach would be more expensive (in direct contradiction to what I wrote here a long time ago)
appreciate the followup.