| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[2]: to_number question
Hi Jonathan
Thank you for the post. It seems to me that we always get further when we post what we understand, even when we are sure that our understanding is incomplete/ subject to error, rather than being afraid to expose our own ignorance. My ignorance will as usual be interposed with yours.
On Fri, 16 Jul 2004 15:54:09 -0400, Jonathan Gennick
<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.
It seems to me that what you describe below is a perfectly normal 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.
It took me a long time (I don't have a comp.sci background) to decide that a sql statement was not a set of instructions to the database to tell it how to find the data I was looking for, but a description of the set of data that I wanted back. This may or may not help your reflection.
> Neither Stephen, nor I are trying to impose execution plans
> on the optimizer.
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
here is an attempt at an example
SELECT to_number(a.a), b.b
FROM
( SELECT a
FROM a
WHERE a.id = 1 ),
as compared with
select to_number(a.a), b.b
from a,b
where a.id=b.id
and a.id=1;
These two queries should be identical (if I haven't screwed up the translation to a straight join). In both cases I'm trying to say that I want records from a and b where the id's are identical (and are equal to 1). I hope that you'll agree that the second query doesn't limit the optimizer to going straight to a, finding all records with an id of 1 and then doing some sort of join to b. Your mental model however imposes this execution order on the database 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:
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.
This I like a lot better (it being close to my understanding :) ). Why have the clause about the database operations at all. What the database does to return the data is an implementation detail (admittedly its kind of an important detail, but consider issuing the equivalent statement against - for example - sql server, or informix. The available operations would be different, the set of 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.
That depends on the query. I think you can visualize my example above with either a subquery, or not. In *most* cases the query could be rewritten without a subquery.
-- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.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 Mon Jul 19 2004 - 04:51:46 CDT
 |
 |