to_number question 2004-07-15 - By Wolfgang Breitling
Quoting Jonathan Gennick <jonathan@(protected) >:
>
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
