RE: Re[4]: to_number question

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Mon, 19 Jul 2004 08:24:02 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5CE6@exchsen0a1ma>

Jonathan,

Please please please keep distracting us with your unease about how things work. I know I learn a lot reading every single stinkin word you put on this list, as well as what you publish.

I know I am never to old to learn.

Thanks again.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Jonathan Gennick [mailto:jonathan_at_gennick.com] Sent: Monday, July 19, 2004 8:01 AM
To: Niall Litchfield
Cc: oracle-l_at_freelists.org
Subject: Re[4]: to_number question

NL> Thank you for the post. It seems to me that we always get further 
NL> when we post what we understand, even when we are sure that our 
NL> understanding is incomplete/ subject to error, rather than being 
NL> afraid to expose our own ignorance.

Thanks Niall. I've been pressing the issue quite a bit actually, but only because I want to work towards an explanation that satisfies my inner soul. It's taken me awhile to get to that point. However, I had a bit of an epiphany over the weekend, and now I've got a draft of an article that I hope will make the whole issue clearer to at least a few people besides myself.

NL> It seems to me that what you describe below is a perfectly normal NL> way of reading a given sql statement - it just isn't correct :) . ...

NL> It took me a long time (I don't have a comp.sci background) to 
NL> decide that a sql statement was not a set of instructions to the 
NL> database to tell it how to find the data I was looking for, but a 
NL> description of the set of data that I wanted back. This may or may 
NL> not help your reflection.

Yes. Someone else said something that got me to thinking along those lines. What you say here is a key point. Unfortunately, I believe the SQL language designers have not done us any favors when it comes to implementing a language and syntax that reflects the proper way of thinking about queries. Verbs such as SELECT and keywords such as FROM seem to work against a correct interpretation of a query by humans.

Bear with me a moment, and let me repeat your two examples. There's more from me down below.

NL>  SELECT to_number(a.a), b.b
NL>   FROM
NL> ( SELECT a
NL>          FROM a
NL>          WHERE a.id = 1 ),
NL>  b 
NL> where b.id=a.id;

NL> as compared with

NL> select to_number(a.a), b.b
NL> from a,b
NL> where a.id=b.id
NL> and a.id=1;

NL> I hope that you'll agree that the second query doesn't limit the 

NL> optimizer to going straight to a, finding all records with an id of NL> 1 and then doing some sort of join to b.

Nor does the first query make that limitation.

NL> Your mental model however imposes this execution order NL> on the database for the first query.

No. Not at all, and this is where I think many people misunderstand me. I expect (or used to expect) that the results would be "as if" the subquery had been materialized first. However, I never required that the subquery actually be materialized. Nor did I require anything else. I only required that, no matter what the database really did, that the end results be the same "as if" the outer query had never seen rows other than those returned from the inner query.

Dan Tow pointed out in one of his earlier notes that it's at least possible to conceive of ways in which the database might operate in the manner I've just described.

The question seems to boil down to whether a row source should be treated as opaque with respect to the rest of the query. Interestingly, joins, as in your example, are part of the epiphany I had over the weekend, which has led to me the "correct" way of thinking. We allow the evaluation of WHERE clause predicates either before or after rows have been joined. This is the same issue, really, as the subquery issue we've been discussing.

If you can accept that a WHERE clause predicate can be applied to a row before that same row is tossed out by a JOIN predicate, and thus the WHERE clause predicate was applied to a row not returned by the join, and thus not in the row source for the query, then you must also accept that a predicate outside an inline view may be applied to a row that is not ultimately returned by that view.

NL> These two queries should be identical

I still don't quite accept this statement. The results are identical. The queries themselves are not. I can only accept the rewritten query as a valid interpretation of the first if I can also rationalize that the first query might result in "where b.id=a.id" being applied before the subquery result set is fully defined. Fortunately<grin>, I'm now able to accept this.

Let me try and be a bit more clear on what I just said:

Query_1 -> optimization -> execution plan

Query_1 -> transformed to query_2 -> execution plan

I can only accept the transformation to query_2 if the same, ultimate execution plan can legitimately result from query_1. I don't like to think in terms of queries being rewritten. This is probably another topic, but I believe the intent of SQL has always been:

Query -> some optimization process -> execution plan

If it helps those who program the optimizer to manipulate and rewrite the SQL in order to come up with various execution plans, that's fine, but such rewriting is, to me, an implementation detail. The execution plan in the cases we've been discussing needs to be (and is, I now believe) reachable without necessarily rewriting the SQL.

I hope I have not driven you all to distraction by the way I think about things. Rest assured, I've managed to adjust my mental model enough to allow for the behavior we've all been discussing.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan_at_gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit
http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Monday, July 19, 2004, 5:55:13 AM, Niall Litchfield (niall.litchfield_at_gmail.com) wrote:
NL> Hi Jonathan

NL> Thank you for the post. It seems to me that we always get further 
NL> when we post what we understand, even when we are sure that our 
NL> understanding is incomplete/ subject to error, rather than being 
NL> afraid to expose our own ignorance. My ignorance will as usual be 
NL> interposed with yours.

NL> On Fri, 16 Jul 2004 15:54:09 -0400, Jonathan Gennick NL> <jonathan_at_gennick.com> wrote:
>> I've been on the fence about whether to post the note below. I think
>> I'll go ahead and post it, because it sums up as best I can why I'm
>> so flummoxed over this issue. It'll give you an idea of where my head
>> is at, though you might feel that I have a rather strange way of
>> looking at things.

NL> It seems to me that what you describe below is a perfectly normal NL> way of reading a given sql statement - it just isn't correct :) .

>> To get any resolution, I'm just going to have to go off in a corner
>> and reflect for awhile.

NL> It took me a long time (I don't have a comp.sci background) to 
NL> decide that a sql statement was not a set of instructions to the 
NL> database to tell it how to find the data I was looking for, but a 
NL> description of the set of data that I wanted back. This may or may 
NL> not help your reflection.

>> Neither Stephen, nor I are trying to impose execution plans on the
>> optimizer.

NL> I think you are - see below

>>
>> With SQL you have the conceptual execution of a query versus the
>> actual execution performed by the database. Having the proper
>> conceptual model in mind is critical, because that's what you use
>> when you actually write a SQL statement.
>>
>> My mental model for subqueries in the FROM clause has
>> always been:
>>
>> The outer SELECT operates against the rows
>> and columns returned by the subquery

NL> here is an attempt at an example

NL>  SELECT to_number(a.a), b.b
NL>   FROM
NL> ( SELECT a
NL>          FROM a
NL>          WHERE a.id = 1 ),
NL>  b 
NL> where b.id=a.id;

NL> as compared with

NL> select to_number(a.a), b.b
NL> from a,b
NL> where a.id=b.id
NL> and a.id=1;


NL> These two queries should be identical (if I haven't screwed up the 
NL> translation to a straight join). In both cases I'm trying to say 
NL> that I want records from a and b where the id's are identical (and  
NL> are equal to 1). I hope that you'll agree that the second query 
NL> doesn't limit the optimizer to going straight to a, finding all 
NL> records with an id of 1 and then doing some sort of join to b. Your 

NL> mental model however imposes this execution order on the database NL> for the first query.

>> Perhaps the correct mental model is:
>>
>> The outer SELECT operates against the rows
>> and columns returned by the subquery, except
>> when the optimizer surprises you by doing
>> otherwise.
>>
>> This is certainly not very satisfactory. At least, I'm not at all
>> happy with it. Better might be:

NL> Indeed, how horrible.

>> The outer SELECT and subquery together describe the state of
>> the data to be returned by the statement, but to get that
>> state the database will perform various operations in some
>> indeterminate order.

NL> This I like a lot better (it being close to my understanding :) ). 
NL> Why have the clause about the database operations at all. What the 
NL> database does to return the data is an implementation detail 
NL> (admittedly its kind of an important detail, but consider issuing 
NL> the equivalent statement against - for example - sql server, or 
NL> informix. The available operations would be different, the set of 
NL> data to be returned would be the same.

 

>> This I like better, but still leaves me vaguely unsatisfied. For one
>> thing, the "state of the data" cannot be properly understood without
>> also visualizing the results from the subquery, so I'm left with a
>> bit of a chicken/egg problem.

NL> That depends on the query. I think you can visualize my example 
NL> above with either a subquery, or not. In *most* cases the query 
NL> could be rewritten without a subquery.

----------------------------------------------------------------

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
-----------------------------------------------------------------
----------------------------------------------------------------
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 Mon Jul 19 2004 - 07:21:02 CDT