Re: Re[2]: to_number question

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 15 Jul 2004 07:06:04 -0700

Quoting Jonathan Gennick <jonathan@xxxxxxxxxxx>:
> 
>    SELECT a, to_number(b)
>    FROM ( SELECT a, b
>           FROM subtest
>           WHERE a = 'a' );
> 
> I admit that I have not gone back to the ANSI standard to
> read in detail what it says about this particular case.
> However, I have a very difficult time understanding how you
> can look at the above query and come up with any other
> explanation of it than the following:
> 
> 1. The subquery defines a *new* table of rows.

No, it does not. That same query can be written in a semantically equivalent 
way without the subquery. And you rightlfully expect the optimizer 
to "effortlessly" convert your query into all of them and pick the one that 
returns the result the festest.
Subqueries are a means to structure a query such that its logic is more easily 
grasped. They are NOT a means to impose a processing order. 
> 

> Is there really something in the ANSI standard that says
> otherwise? Does relational theory really allow the outer
> query to execute against rows that are not in the "table"
> I've listed in my FROM clause?

Yes. You are under a misconception about what the table in your from clause is. 
It is ALWAYS the base physical table(s). ANY row in that table may be accessed 
in ANY order and subjected to the projection, filter(s), and join(s).

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
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@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: