A Cure for Madness 2004-08-29 - By Wolfgang Breitling
At 12:14 PM 8/29/2004, you wrote:
>Wolfgang,
> I defintely agree with you on some points.
> First, (as Jonathan pointed out to me), Date does mention that
> merging should be allowed, specifically for the sake of performance.
That 's not what he says in the article. There he gives an "unequivocal no "
to the question whether it is valid to rewrite the query.
> Second, he goes on to explain that the merging done by the cbo
>should/can
> be done as long as it does not cause an error or change the intended
>result
> of the query (that 's the part I don 't like).
> So, I think he 's saying (as was Jonathan originally),=20
> sure, go ahead and merge as long as the cbo "knows " what we want to do
>and=20
> the merging does not cause an error (even if it 's our error).
I don 't like the "as long as it does not cause an error " part. How can you
know in advance what causes an error. Is the optimizer not allowed to use a
SM join because it might run out of sort space?
The current rules are pretty simple - the optimizer can rewrite the query
as long as the end result (I 'd avoid the term "intended " - sometimes, and
not even that rarely, the "intended " result and the actual result of the
query as written are not the same) does not change.
> In Date 's "An Introduction To Database Systems 7th ed. ", chapter 17
>(Optimization),
> he walks through some simple examples.
> On page 539:
> =20
> ( ( SP JOIN S ) WHERE P# =3D P# ( 'P2 ' ) ) { SNAME }
>
>
> based on the order of how the query was written the cbo would:
> 1. join sp and s on p# (getting all the matches!!)
> 2. then restrict the P2 tuples
> 3. finally project sname
>
> Date himself (on that same page) suggests the cbo should instead:
> 1. restrict sp to only the P2 tuples
> 2. join that result to s on p#
> 3. finally, project sname
>
> He gives another example on pg. 545 as well. It 's obvious query rewrite
>can be beneficial.=20
> He does however state (in the book and article)
> that these optimizations should be done without changing the resultset=20
> ( "changing " in regards to the article caused the Invalid Number error).
This is where I disagree. The error was not caused by rewriting the query
but by the fact that the function used is not well defined over the domain
of the argument, which is what I attempted to show with my "my_to_number "
function.
> My question is, does the merging we notice really change the resultset?
> I don 't think so, it barfs because you want to perform a number
>comparison on a string.
My point exactly
> Should the cbo have to handle stuff like that???
No. It has enough on its hands to optimize sql, it should not compensate
for our mistakes. I am annoyed enough by MS Word trying to second-guess my
"intend ". I 'd much rather it stick to do what I ask it to do.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
