RE: optimizer rewriting query
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tanel.poder.003@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 13 Mar 2005 07:53:05 -0500
This thread has interesting implications regarding the motivation to see the
"rewritten" query. Tanel already made the main point, but I think a bit more
is useful to consider.
I get the impression that the original poster is thinking that the rewrite
is in some way qualitatively better than original query and maybe is
thinking to substitute Oracle's rewrite in the original code.
We cross an interesting boundary when we switch from humans reading queries
to feeding queries to automata. If you're just interested in how the engine
works, great. But if Oracle can get a good plan from what you hand to it,
then the plan that most naturally represents and documents the intent to a
human being is far superior. There is some small possibility that Oracle's
rewrite is actually better from this standpoint, presuming you put back in
lowercase and indentation formatting, but I'm guessing that if they
generated the text representing the rewrites it would tend to be ugly. We
should try to reserve ugly rewrites for when Oracle gets a suboptimal plan
from the natural statement of the query. In fact I consider it a good
practice to retain the natural problem statement as commentary or
documentation with an explanation. When the original (un-tuned) natural
query statement gets an equally good plan in a future improvement of Oracle,
you can then stop including similar unnatural permutations in new code.
(Unless the former permutations are now inferior I usually leave them alone
in the name of code stability, but try to update the documentation to note
the permutation is no longer needed after version xxxxxx).
At Hotsos 2005 Martin Buechi gave a wonderful paper "Effective Dynamic SQL."
One small part of that paper included differentially generating the query to
be parsed based on a sampling and shows that in some cases this reliabily
results in the generation of superior execution plans compared to peeking.
Martin underscored in his presentation that the motivation is to force the
creation of a new execution plan, and that when the simple problem statement
works well you should stick with it. Having personally written some awful
looking complicated isofunctional query permutations over the years in order
to get a good plan, I try to remember that the purpose is temporary to
handle cases beyond the current and ever improving state of the art of the
optimizer. Usually when Oracle "rewrites" your query, that means you may
leave the natural statement in your code.
CDTA,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tanel Põder
Sent: Sunday, March 13, 2005 4:29 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: optimizer rewriting query
Hi,
Oracle breaks the query into a parse tree in order to make it understandable
and optimizable to it - there is no point to put the optimized query back
to SQL text format again, at least from Oracle database engine point of
view, execution uses the parsed & compiled structures anyway.
<snip>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
