Re: feature & performance comparison

<flame>

With orthogonality is meant to use SQL and relational concepts to their fullest and not to add features that are bound to be restricted and non scalable. Examples (And these are just things I happened to bump into, random sampling):

INSERT .. RETURN....
This is a special case statement that does not scale. If I want to insert a page of data (e.g. from a screen) I end up doing single row inserts. This isn't SQL. This is procedural. It isn't even defined what happens to before or after triggers. It's not allowed on instead of triggers. What about UPDATE RETURN or DELETE RETURN (to get out what I'm deleting)?

MULTI TABLE INSERT (brand new Oracle 9i) This is the proof point that INSERT with RETURN wasn't the right approach. If INSERT could return result sets instead of simple values one could stack those puppies. What Oracle is doing here will not fly far in an DW environment it is not orthogonal. Interestingly the docs don't mention in which order triggers fire in this case. Are they allowed? What is this strange 999 column restriction, btw?

Even all these things put aside I don't understand the need for this special feature if Oracle isn't after a relational extention. Can't all this be written in procedural PL/SQL? In SQL/PSM standard this looks like this: BEGIN ATOMIC
  FOR insrow AS <query> DO
    IF <cond1> THEN INSERT INTO T1 ... ...     ... END IF;
  END FOR;
END This doesn't require any more language and supports full function. If it doesn't perform the DBMS needs fixing and not the language.

CONNECT BY
Last time I read up on that (to help an ISV migrate) it seemed a lot more restrictive than a recursive common table expression which is more relational. Needless to say that common table expressions are in the standard but not CONNECT BY.

SEQUENCES
CURRVAL is CURRVAL ... or NEXTVAL? Let me guess: This got into Oracle before Oracle supported nested queries. There is no reason I can think of why these wacky semantics ar eneeded. It cannot be used in a where clause, but it can be used in a function which is used in that same where clause. What is the semantics? Where is it defined?
It uses the same name space as tables because its referenced like a table. But you can't put it into the FROM....
I tried to get that information out of the Oracle newsgroup: "Don't do that", "Why would you want to do this?", "this isn't good" Were the answers I received.
Please ask your standrd rep what he thinks about this. He is currently trying to carry it to the standard.

OBJECT TABLES (extended in Oracle 9i)
OO interested as I am I read up on object views and object tables. Object views seem to follow largely SQL99. I don't see how object tables fit in. Can't you create subtables? Shouldn't they look the same? It was defined in SQL99. Instead Oracle has "[NOT] SUBSTITUTABLE AT ALL LEVELS", huh? Where is the problem? How will you allow to define RI, Triggers, Constraints on subtables (or instances of subtypes)?

Oracle has many important features, but often this DBMS seems to be designed based on the specific needs a customer had at a given time without the vision needed to fit them into the big relational picture for years to come. There is good reason why IBM (and thus DB2) dominates the standards.

Oracle touts that it supports standards. It doesn't seem to care about the SQL standard even for new features (such as object tables), however..... Interesting.
</flame>

Serge Received on Sat Jun 09 2001 - 21:08:20 CDT