Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Cure for Madness

RE: A Cure for Madness

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sun, 29 Aug 2004 15:29:08 -0600
Message-Id: <6.1.0.6.2.20040829145926.03083dd0@pop.centrexcc.com>


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_at_freelists.org 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
-----------------------------------------------------------------
Received on Sun Aug 29 2004 - 16:24:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US