| "+0 trick undesirable", why? [message #205151] |
Thu, 23 November 2006 09:08  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
All,
In Jonathan Lewis's book "Cost Based Oracle Fundamentals", he writes (in the context of avoiding a certain index to be used):
| Quote: | "An [...](undesirable) option is the good old rule-based trick - changing the join predicate to t2.n1=t1.n1+0. Unfortunately, when you adopt this worst-practice approach, the optimizer gets the right join cardinality and a sensible cost".
|
It might be my (lack of) understanding of the English language, but I read this like:
| Quote: | I think this is a bad thing, yes it does what you want, and I don't like the fact that it does what you want because that will tempt you to use something that's bad.
|
Now I'm confused! Why is the +0 such a "worst practice" approach?
I tried to find a reason for it on his website, but haven't find it. Anyone here any suggestions? (and perhaps arguments why Jonathan is right or not?)
Regards,
Sabine
PS To avoid some misunderstanding: the rest of the (>500 pages) book is just excellent, brilliant, etc.! But this tiny remark stands out because there is no explanation or reasoning to be found, whereas any other subject is being analyzed to the tiniest detail.
|
|
|
|
| Re: "+0 trick undesirable", why? [message #205177 is a reply to message #205151] |
Thu, 23 November 2006 14:30  |
sebastianR
Messages: 33
Registered: August 2005
|
Member |
|
|
My blind guess would be, that using the rule-base optimizer itself could be the "bad" choice, so as this "trick" (whatever it does exactly) is based on the RBO, don't use it(?).
I'm not sure, but I think it's recommended to use the CBO (cost based optimizer), since
1) the RBO won't be supported long-time (maybe wrong!)
2) and I never used it yet
3) and I don't know when or why to use it
As you can see my knowledge about the RBO is tremendous, ...
Maybe someone could point out if and when to use RBO and why this trick above is "bad"?
regards,
Sebastian
|
|
|
|
| Re: "+0 trick undesirable", why? [message #205202 is a reply to message #205177] |
Thu, 23 November 2006 20:11 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When we have a predicate INDEX_COL = val, the CBO has a choice: should I use an index or not. It takes a stab based on how many rows are in the table, how many rows it thinks have a value of 'val', and how this compares with other possible access paths for the table.
If it WRONGLY chooses to use an index, it means that one of these assumptions is wrong. By making the predicate INDEX_COL+0 = val, we prevent Oracle from using the index. Great, but what else has happened?
Even though we are clever enough to realize that adding zero does not alter a numeric column, Oracle is not so clever. Prior to this change, Oracle could provide a reasonable guess as to how many rows would match 'val', but now its job is harder, and that will affect the estimate.
Even though it is not using the index, the estimate of matching rows will affect the query in other ways: namely the join-order and join-methods. Your index-workaround may not only prevent an index-scan, it may change a Hash Join to Nested Loops, or join that table last instead of first, or any other number of possibilities.
A better way is to work out WHY Oracle wanted to use the index in the first place. If a row-estimate was wrong, you might re-analyze the table/index, or use a CARDINALITY hint. If all else fails, use a NO_INDEX hint.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
