RE: optimizer rewriting query

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tanel Põder Sent: Sunday, March 13, 2005 4:29 AM
To: oracle-l_at_freelists.org
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>

--
http://www.freelists.org/webpage/oracle-l