Re: Re[2]: to_number question

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 19 Jul 2004 10:55:13 +0100

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@xxxxxxxxxxx> 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 ),
 b 
where b.id=a.id;

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: