Re: Re[4]: to_number question

 I caught the thread a bit late but I fully agree with Wolfgang. In my view, one of the main flaws of the SQL language is that it combines, unfortunately pretty transparently for most developers, two utterly distinct personalities (Schizophreniac Query Language), one which is purely relational, a world where we only live for the current row (I am going to be frowned upon by purists - the current relvar) and where we are blissfully unaware of other rows and any relationship we may have to them, whether it is in terms of order, fraction of the full set or whatever, and one which is basically a reporting language which operates on fully known sets of rows we can order, sum up, and so on - in other words filled arrays. GROUP BY and CONNECT BY aresomewhere in between, not operating on arrays but nevertheless having some'consciousness' of other rows.
The sad fact is that business requirements usually call on both sides of the language, and that ensuring that the purely relational side comes into play first isn't always easy, especially when the CBO kicks in. Regards,

Stephane Faroult

On Thu, 15 Jul 2004 06:51 , Wolfgang Breitling <breitliw_at_centrexcc.com> sent:

Quoting Jonathan Gennick <jonathan_at_gennick.com[1]>:

[snip]
>The target "table" for the outer query is that set of rows
>returned from the inner query. Thus, the outer query *must*
>consider only those rows. It's a fault, in my current
>opinion, for the outer query to look at any row not returned
>by the inner query.
>

You may disagree, but those are the rules. The optimizer - and we are not talking about the cost based optimizer specifically here, ANY optimizer - breaks the ENTIRE sql down into this basic set of relational operators, no matter how you wrote it initially, and then is free to reorder them any which
way because it relies on the commutativity. If you bring something into play

that violates the commutativity, YOU are not playing by the rules, not the optimizer.

-- 
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[2]
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org[3]
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/[4]
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html[5]
-----------------------------------------------------------------



--- Links ---
   1 javascript:parent.opencompose('jonathan_at_gennick.com','','','')
   2 modules/refer.pl?redirect=http%3A%2F%2Fwww.orafaq.com
   3 javascript:parent.opencompose('oracle-l-request_at_freelists.org','','','')
   4 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle-l%2F
   5 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fhelp%2Ffom-serve%2Fcache%2F1.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
-----------------------------------------------------------------