RE: HI

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Mon, 20 Sep 2004 21:03:50 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNKEOACIAA.lex.de.haan@naturaljoin.nl>

the crux in the subquery madness debate was that SQL is missing functionality.
what we have in mathematics is the concept of composition. For example,

f(g(x)) means apply function g on x *first* and then apply function f on the result.
f(g(x)) is not the same as g(f(x)) in general.

this is elegant maths syntax to express precisely what you want to happen; in the subquery madness example, such syntax could make sure that you *first*
filter column values *before* you try to apply a TO_NUMBER conversion. however, SQL doesn't make this clear distinction

aaarrrggh, there you go -- it was *not* my intention to open that debate again...

the operators from the relational algebra (like the restriction example, given below)
indeed offer full freedom in their processing order.

Kind regards,
Lex.

---

visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

---

skype me <callto://lexdehaan>

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: Monday, September 20, 2004 19:45
To: oracle-l_at_freelists.org
Subject: Re: HI

I thought Oracle (and db2 and mssql) are relational dbs (with some object features veneered on). The crux as I see it is that the SQL language is not built on as rigorous a foundation as the relational algebra and as such let quite a bit of ambiguity creep in.

Rethinking my statement above, you may be right that they all are not strictly relational DBs in that relational algebra does not have a concept of duplicates. Every tuple is uniquely identifiable.

Just out of curiosity I dug out the course material of a course on Database Design by "The Relational Institute". The material has "copyright 1987 Codd & Date consulting group" on every page. In the chapter on Query Optimization under the heading "convert into canonical form" it lists several transformation rules:

• (A join B) where A-restriction and B-restriction ==> (A where A-restriction) join (B where B-restriction)
• any restriction predicate ==> equivalent predicate in conjunctive NF e.g. P or (Q and R) ==> (P or Q) and (P or R)
• (A where restriction 1) where restriction 2 ==> (A where restriction-1 AND restriction-2) I love this particular one the most. Isn't that exactly the case where he was arguing in response to Jonathan's subquery madness article that the subquery must be evaluated before the outer restriction is applied???
• (A [projection-1]) [projection-2] ==> (A [projection-2])
• (A [projection]) where restriction ==> (A restriction) [projection]
• A.F1 > B.F2 and B.F2 = 3 ==> A.F1 > 3 (possibly allows the elimination of a join!!)
• others

At 12:15 PM 9/20/2004, you wrote:
>You probably are correct Wolfgang, but I thought Oracle is NOT a
>relational db, but an
>SQL db, and if I understand Date correctly, then this qst has relevance.
>
>--
>Rob Zijlstra

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 20 2004 - 13:59:32 CDT