What do Oracle professionals think of Fabian Pascal?

[Paul]

I have been visiting the site www.dbdebunk.com recently and have found
the musings of the author interesting. Having seen some disasters in
my time (in terms of adherence to any sort of reasonable database
design), I agree with a lot of what he has to say. I'm not sure that I
completely understand his constant criticism of SQL (more reading
required maybe?).

He is constantly railing against vendor extensions (which, from what
I've read, he would see as corruptions) of the Relational Model,
however my own thougts would be that a competent professional can say
to himself "Right, this isn't fully compliant, but it does what I want
quickly and easily, so I'll use it until something better comes
along".

I would be interested in the opinions of other posters in this group,
particulary from those who have posted stuff in the past about the
need for the data-management industry to get itself some decent
independent standards prevalent in other industries (medical, legal,
architecture...).

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[DA Morgan]

Paul wrote:
>
>
>
> I have been visiting the site www.dbdebunk.com recently and have found
> the musings of the author interesting. Having seen some disasters in
> my time (in terms of adherence to any sort of reasonable database
> design), I agree with a lot of what he has to say. I'm not sure that I
> completely understand his constant criticism of SQL (more reading
> required maybe?).
>
>
> He is constantly railing against vendor extensions (which, from what
> I've read, he would see as corruptions) of the Relational Model,
> however my own thougts would be that a competent professional can say
> to himself "Right, this isn't fully compliant, but it does what I want
> quickly and easily, so I'll use it until something better comes
> along".
>
> I would be interested in the opinions of other posters in this group,
> particulary from those who have posted stuff in the past about the
> need for the data-management industry to get itself some decent
> independent standards prevalent in other industries (medical, legal,
> architecture...).
>
>
>
> Paul...

Purists (fanatics) in any endeavour should be treated with disdain.

The fact that an extension exists is neither a good thing nor a bad
thing ... it is just a thing. The question should be one of whether
said extension helps solve real-world business problems.

Last time I looked no one from an ANSI committee asked me, or my
business and academic associates, what we needed in the way of
functionality: Oracle has and Oracle has delivered.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[fabian pascal]

Paul,

1. I say nothing about "vendor extensions" as such. My criticism is of
any violation or dismissal of relational principles which is completely
different. If an extension does that, then it's a bad idea.

2. Where you are mistaken is that a vast majority of professionals are
not competent as far as data fundamentals are concerned. They may know
programming or Oracle, but they have little clue of database and
relational fundamentals. Therefore, they cannot assess products or
features properly and confuse what the product does with the correct
way of doing it. It is one thing to be aware of the problems, but use
them because you have no choice (and know what consequences to expect),
and another to be oblivious to the problems.

So be very careful with the replies you receive from Oracle
professionals. Morgan is an excellent example. For my comments on his
competence see:

http://www.dbdebunk.com/page/page/1770593.htm
http://www.dbdebunk.com/page/page/1477865.htm

The relational model is application of science--logic and math--to
database management. The notion that one is "fanatic" about that is
tantamount to claiming that civil engineering is fanatical about the
laws of physics. Such a claim reveals problems more serious than
ignorance of data fundamentals, and THAT is what deserves disdain.

[fabian pascal]

For opinions on Fabian Pascal by Oracle professionals who also are
competent on fundamentals (and can reason properly) see the Dizwell,
Tom Kyte, Jonathan Lewis. They sometimes comment on dbdebunk.

[DA Morgan]

fabian pascal wrote:
> Paul,
>
> 1. I say nothing about "vendor extensions" as such. My criticism is of
> any violation or dismissal of relational principles which is completely
> different. If an extension does that, then it's a bad idea.

Why? Because you are a purist or for some reason that supports the
business use of the tool?

> 2. Where you are mistaken is that a vast majority of professionals are
> not competent as far as data fundamentals are concerned. They may know
> programming or Oracle, but they have little clue of database and
> relational fundamentals.

No argument here. But the ignorance, or lack thereof, is irrelevant to
whether a vendor adds an extension to their product (unless it is a
help system).

> Therefore, they cannot assess products or
> features properly and confuse what the product does with the correct
> way of doing it. It is one thing to be aware of the problems, but use
> them because you have no choice (and know what consequences to expect),
> and another to be oblivious to the problems.

Again ... true but irrelevant.

> So be very careful with the replies you receive from Oracle
> professionals. Morgan is an excellent example. For my comments on his
> competence see:
>
> http://www.dbdebunk.com/page/page/1770593.htm
> http://www.dbdebunk.com/page/page/1477865.htm
>
> The relational model is application of science--logic and math--to
> database management. The notion that one is "fanatic" about that is
> tantamount to claiming that civil engineering is fanatical about the
> laws of physics. Such a claim reveals problems more serious than
> ignorance of data fundamentals, and THAT is what deserves disdain.

Your self-aggrandizement goes beyond even that I am capable of and I
have never been accused of being shy.

The rules of physics were not created by physicists ... they were
created by (take your pick) the underlying principles by which the
universe exists or god. Last time I checked relational algebra was
man made and both Codd and Date were human.

A database is a tool. When the use of the tool provides value to a
business or organization it is a plus: When it does not it is a
negative. That the person wielding the hammer is more or less competent
is a diversion.

That an object-extension does not fit cleanly within some theoretical
framework is irrelevant to anyone except a purist. If use of that
object-extension allows the more accurate modeling of a business
requirement and interface with an OO front-end it is a plus. If it does
not then it should die on the drawing board.

There are plenty of places where, in the real world, relational theory
breaks down when it comes to application. Not once have I seen fifth
normal form in a production application: Have you?

[fabian pascal]

Sorry, but you did not understand one word I said. And I am not
surprised, because you don't have the necessary knowledge. What is
more, there is too little intellect and reasoning ability there for me
to bother, because I won't have any effect.

I will let the readers who have the necessary capacity and knowledge to
judge who makes sense.

[joel-...@home.com]

http://www.dbdebunk.com/page/page/2089650.htm shows that he still
really thinks everyone is stupid and has a low opinion of the great
unwashed masses. I seemed to have got some small measure of fame from
upbraiding him about this 15 years ago on compuserve, when he was
abusing some poor soul because his damagement weren't caring about
whether database products had completely implemented the relational
model.

Aside from not respecting the average geek, I think he is right on
technically and theoretically. Don't see how to get there practically
(although we can hope Tom Kyte can make it so).

>He is constantly railing against vendor extensions (which, from what
>I've read, he would see as corruptions) of the Relational Model,
>however my own thougts would be that a competent professional can say
>to himself "Right, this isn't fully compliant, but it does what I want
>quickly and easily, so I'll use it until something better comes
>along".

So are you, in his words, "oblivious to the problems" or not?

I can say I am pretty much oblivious to the problems, since "the
marketplace" wants results from me, so how to do that is the stuff I
tend to remember and care about. Since I started with relational
databases before SQL was the, um, standard data interchange language, I
do sometimes wish things were different, in the manner Fabian
advocates. Then I run into this XML crap and go "What the heck do we
do now???"

jg
--
@home.com is bogus.
http://pluto.jhuapl.edu/ecard/scripts/addSignaturesForm.php

[fabian pascal]

>http://www.dbdebunk.com/page/page/2089650.htm shows that he still
really thinks everyone is stupid and has a low opinion of the great
unwashed masses. I seemed to have got some small measure of fame from
upbraiding him about this 15 years ago on compuserve, when he was
abusing some poor soul because his damagement weren't caring about
whether database products had completely implemented the relational
model.

Do you think I have any REASON to think that? Have I taken this
position WITHOUT ANY EVIDENCE? Or is my LOGIC in claiming that wrong?
If so, defend your position. Just claiming things without evidence and
ignoring the fact only proves my position.

[joel-...@home.com]

>Do you think I have any REASON to think that? Have I taken this
>position WITHOUT ANY EVIDENCE? Or is my LOGIC in >claiming that wrong?
If so, defend your position. Just >claiming things without evidence and

ignoring the fact only proves my >position.

What logic? You state most people in the industry are uninformed,
80%-90% of pronouncements are wrong. Where do you get such statistics?
Where is your evidence? Do you not see a problem with telling people
to claim things with evidence while you make stuff up?

I personally agree with many of the statements, but to make up
statistics... well, I don't think Mr. Lewis or Mr. Kyte would take
kindly to that, unless maybe you clearly stated you were making them
up.

jg
--
@home.com is bogus.

http://ask.slashdot.org/article.pl?sid=05/08/26/1739234&tid=187&tid=126&tid=4&tid=218

[fabian pascal]

> What logic? You state most people in the industry are uninformed,
> 80%-90% of pronouncements are wrong. Where do you get such statistics?
> Where is your evidence? Do you not see a problem with telling people
> to claim things with evidence while you make stuff up?

I have a site, several columns and two books chockful of evidence and
you ask where it is? Do you expect me to feed all that stuff to you in
a thread? If you want to criticize my position, first educate yourself
on it, THEN state your case. I did not come online and made claims
about you, you did. SO it is incumbent on you to do the homework, if
you want to be taken seriously.

[DA Morgan]

I fail to see a problem with 90% of what he rails against. I go into the
hardware store and I see a screw driver. I need to drive in a nail so
buy a hammer. I use the tool appropriate for accomplishing the job.

I see this as a hammer bigot saying ... hammers were invented first
(well rocks really) so you've got to use a hammer.

But XML in a database ... just another example of the only tool you need
is a hammer. XML has its uses. Storing data is not one of them.

[DA Morgan]

So, in other words, you feel fully justified with pointing an accusatory
finger with one hand while committing the exact same sin with the other.

That some people disagree with you does not mean that they have the
opinions you attribute to them. Nor do I suspect anyone is impressed
that you feel it appropriate to regurgitate the fact that you did
something 15 years ago.

You've definitely made a point. Likely, though, not the one you
intended.

[DA Morgan]

fabian pascal wrote:
>>What logic? You state most people in the industry are uninformed,
>>80%-90% of pronouncements are wrong. Where do you get such statistics?
>> Where is your evidence? Do you not see a problem with telling people
>>to claim things with evidence while you make stuff up?
>
>
> I have a site,

And an anonymous one too: Impressive. And of course we all believe
everything we read on the internet. For example I believe this:
http://www.theonion.com/news/index.php?issue=4133&n=2

several columns and two books chockful of evidence and
> you ask where it is?

Seemed like a reasonable question. You've got columns? What columns?
What publictions? What pages? You've got books? Heck I've got books.
My mother has books. Likely she has more books than you too.

Do you expect me to feed all that stuff to you in
> a thread?

Unless you want people to draw the conclusions they are drawing
I think the answer is that they do.

If you want to criticize my position, first educate yourself
> on it, THEN state your case. I did not come online and made claims
> about you, you did. SO it is incumbent on you to do the homework, if
> you want to be taken seriously.

He is being taken seriously. You are the one that appears to be
posturing. You may well be correct in everything you've said. But
based on your posts I'd have to give you low marks on making your
case. So far zero facts, zero references, lots of vague allusions
bereft of content.

[joel-...@home.com]

DA Morgan wrote:
> fabian pascal wrote:
> >>http://www.dbdebunk.com/page/page/2089650.htm shows that he still
> >
> > really thinks everyone is stupid and has a low opinion of the great
> > unwashed masses. I seemed to have got some small measure of fame from
> > upbraiding him about this 15 years ago on compuserve, when he was
> > abusing some poor soul because his damagement weren't caring about
> > whether database products had completely implemented the relational
> > model.
> >
> > Do you think I have any REASON to think that? Have I taken this
> > position WITHOUT ANY EVIDENCE? Or is my LOGIC in claiming that wrong?
> > If so, defend your position. Just claiming things without evidence and
> > ignoring the fact only proves my position.
>
> So, in other words, you feel fully justified with pointing an accusatory
> finger with one hand while committing the exact same sin with the other.
>
> That some people disagree with you does not mean that they have the
> opinions you attribute to them. Nor do I suspect anyone is impressed
> that you feel it appropriate to regurgitate the fact that you did
> something 15 years ago.

Actually, I was the one who made the 15 years ago statement. Fabian
doesn't seem to know how to quote. I have trouble with google not
formatting exactly how it appears it is going to during post
composition, but at least it leaves some of my greater-than signs in
the text so it can be figured out.

>
> You've definitely made a point. Likely, though, not the one you
> intended.

I just wish he would have directly answered my points, for good or ill.
And he didn't seem to read my entire post. His tool for dicing
tomatoes appears to be a hammer.

jg
--
@home.com is bogus.

http://www.signonsandiego.com/uniontrib/20050830/news_1b30ponzi.html

[DA Morgan]

How could anyone decide who was correct and who was not when he replies
with belicose posturing? "I've got information" communicates nothing of
value. Pretending that it is an imposition to reply with specifics
is an indication that there may be little except smoke and mirrors.

[Martijn Tonies]

> I fail to see a problem with 90% of what he rails against. I go into the
> hardware store and I see a screw driver. I need to drive in a nail so
> buy a hammer. I use the tool appropriate for accomplishing the job.
>
> I see this as a hammer bigot saying ... hammers were invented first
> (well rocks really) so you've got to use a hammer.
>
> But XML in a database ... just another example of the only tool you need
> is a hammer. XML has its uses. Storing data is not one of them.

Then again, I sometimes use a wrench to do some hammering. Not
good for the wrench. A hammer would have been better :-)
And I must admit, I've never seen a better tool than a hammer for
nailing, well, nails :-)

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

[joel-...@home.com]

>And I must admit, I've never seen a better tool than a hammer for
>nailing, well, nails :-)

Having built numerous decks and gazebos and shelves attached to
concrete, and watched many houses being built (including 3 next door to
me, currently), a good rule of thumb [ouch] is, a nailgun is far
superior to a hammer.

jg
--
@home.com is bogus.

For both nailing nails and making interesting x-rays.
http://www.sky.com/skynews/article/0,,30200-13081259,00.html

[Martijn Tonies]

> >And I must admit, I've never seen a better tool than a hammer for
> >nailing, well, nails :-)
>
> Having built numerous decks and gazebos and shelves attached to
> concrete, and watched many houses being built (including 3 next door to
> me, currently), a good rule of thumb [ouch] is, a nailgun is far
> superior to a hammer.

A nailgun is one of the things I expected in a reply to this, true :-)

But ... a nailgun only does one job as well.

[Chris ( Val )]

joel-...@home.com wrote:
> >And I must admit, I've never seen a better tool than a hammer for
> >nailing, well, nails :-)
>
> Having built numerous decks and gazebos and shelves attached to
> concrete, and watched many houses being built (including 3 next door to
> me, currently), a good rule of thumb [ouch] is, a nailgun is far
> superior to a hammer.

I would have to agree that a nail gun is an efficient tool,
however, a hammer is a universally recognised tool that does
not require a degree to use, and it can be invoked from any
angle by anyone :-)

In desparate times of need, any object with sufficient weight
can be transformed into a hammer :-)

Cheers,
Chris Val

[DA Morgan]

And its pretty hard to throw a nail-gun at someone to get
their attention. ;-) Ouch indeed.

[HansF]

On Thu, 01 Sep 2005 15:28:27 -0700, Chris ( Val ) interested us by
writing:

> In desparate times of need, any object with sufficient weight
> can be transformed into a hammer :-)

Likewise, in desparate times, a hammer can substitute for a screw driver.

Non-optimal solution, though ...

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

[joel-...@home.com]

> But ... a nailgun only does one job as well.

And that is the essence of the _unix way_. String together the results
of efficient tools for an elegant result.

jg
--
@home.com is bogus.

A ton of jello won't make a good hammer any place you'd want to be
naked.

[Martijn Tonies]

> >>>And I must admit, I've never seen a better tool than a hammer for
> >>>nailing, well, nails :-)
> >>
> >>Having built numerous decks and gazebos and shelves attached to
> >>concrete, and watched many houses being built (including 3 next door to
> >>me, currently), a good rule of thumb [ouch] is, a nailgun is far
> >>superior to a hammer.
> >
> >
> > I would have to agree that a nail gun is an efficient tool,
> > however, a hammer is a universally recognised tool that does
> > not require a degree to use, and it can be invoked from any
> > angle by anyone :-)
> >
> > In desparate times of need, any object with sufficient weight
> > can be transformed into a hammer :-)
> >
> > Cheers,
> > Chris Val
>
> And its pretty hard to throw a nail-gun at someone to get
> their attention. ;-) Ouch indeed.

On the other hand, you CAN use a nail-gun to get someones
attention without throwing it ... Those nails have some reach!

;-)

[striebs]

Paul and others ,

Lets examine an example of Oracle extensions to SQL and commercially
expedient solutions for problems .

1) Extensions : eg ROWID and ROWNUM pseudo-columns

Rowid is a transient identifier of a row of a base table which as we
know happens to correspond to the address of the physical
representation of the row (storage).

So what do Oracle hope to achieve (other than performance) by exposing
an implementation detail from the physical data model through the
logical data model ?

In general to provide an even more convenient way to update or delete a
single row than having to specify match conditions for all key columns
in the WHERE clause and specifically to facilitate a method for
updating absolutely identical rows (duplicates) .

eg consider a table with 2 identical rows which could arise because SQL
does not insist a table has a key (which is an RM (relational model)
requirement) . How can you update or delete 1 of those rows ?

UPDATE table_name SET col1=val1 WHERE ..... AND ROWNUM =1
UPDATE table_name SET col1=val1 WHERE ROWID = &VAR / WHERE CURRENT
OF CURSOR

Now here is the key point , is it better to provide extensions to allow
processing of individual duplicated rows or would it have been better
to outlaw duplicate rows by insisting each table has a key ?

The features which provide row level processing just do not incentivise
people to elevate their thinking to consider sets of data rather than
individual objects and this is holding data management back .

2) Constraint Support

Did you know that a constraint is any condition you can think of which
must evaluate to true (in binary logic) in order for an update of the
database to be plausible ? (in SQL's inconsistent 3 valued logic it
must evaluate to NOT FALSE ) .

Is there anyone who has contributed to this thread who would not like
Oracle to support more constraints than it does and thinks Oracle
constraint support is adequate as it stands ?

Eg i) a declarative constraint to ensure each invoice must have at
least 1 line item (which can only be simulated with a constraint on a
materialised view which outer joins invoice_header and
invoice_line_item ) .
eg ii) to ensure that the key values of 3 tables are disjoint - ie a
key value is unique accross more than 1 table
eg iii) an exclusion constraint to match the FK inclusion constraint eg
to ensure that a key value does not exist in another table .

To my knowledge the only enhancement to constraints Oracle has made
since 7.3 is deferring checking to commit time . I welcome this
expedient solution but through studying recognise that it is
problematic and have become aware that it is not even the only way DBMS
products could address the problem .

7.3 is about 13 years old , zoom forward to 2018 - how confident are
you that Oracle will have significantly enhanced it's constraint
support by then ?

3) Data-types and domain support

In Oracle User defined datatypes are difficult to define and use .

Wouldn't you like to be able to do rudimentary things simply like
define a datatype of country_code based on a built-in datatype such as
varchar2(2) and then add a column to a table as follows ADD
(nationality TYPE country_code NOT NULL) and be able to deal with it AS
CONVENIENTLY as if it has been defined as VARCHAR2(2) ?

To answer you original question I think that Fabian Pascal , Chris Date
, Hugh Darwen , David Mc Goveran are the first authors we need to read
in order to understand our subject .

[fabian pascal]

It's quite telling -- and very predictable -- how quickly a discussion
on data fundamentals is veered by people who don't know and don't care
about fundamentals--read: the vast majority--to a "funny" exchange on
hammers and wrenches. It's happened again and again online and it does
quite a lot to validate my claims about the state of knowledge and
intellect in the industry.

It's only natural.

[fabian pascal]

In fact, it would have probably been better if some would stick to
hammers and wrenches and leave database management to those who are
capable to distinguish between the two areas.

[mikharaki...@yahoo.com]

striebs wrote:
> 1) Extensions : eg ROWID and ROWNUM pseudo-columns
>
> Rowid is a transient identifier of a row of a base table which as we
> know happens to correspond to the address of the physical
> representation of the row (storage).

You are correct about ROWIDs, exposing them to the user achieves
nothing.

ROWNUM is a hack that predated analytical SQL extensions, and some of
these extensions do make sence for an end user. Otherwise, how do you
express the query

"list all the employees with their salary ranks"

?

> 2) Constraint Support

> Eg i) a declarative constraint to ensure each invoice must have at
> least 1 line item (which can only be simulated with a constraint on a
> materialised view which outer joins invoice_header and
> invoice_line_item ) .

Why "simulated"? This is pretty legitimate way to enforce complex
constraints (aka ANSI standard assertions). The method can be traced
back to the 80s when database recearch has been pretty much alive;
check out the papers on materialized view refresh.

Oracle materialized view support might have a lot to be desired, but
that's a different story.

> 7.3 is about 13 years old , zoom forward to 2018 - how confident are
> you that Oracle will have significantly enhanced it's constraint
> support by then ?

Constraint enhancements is not a top priority nowadays. Wake up: the
bleading edge of research and development is XML, Fusion, whatever.

> 3) Data-types and domain support
>
> In Oracle User defined datatypes are difficult to define and use .
>
> Wouldn't you like to be able to do rudimentary things simply like
> define a datatype of country_code based on a built-in datatype such as
> varchar2(2) and then add a column to a table as follows ADD
> (nationality TYPE country_code NOT NULL) and be able to deal with it AS
> CONVENIENTLY as if it has been defined as VARCHAR2(2) ?
>
> To answer you original question I think that Fabian Pascal , Chris Date
> , Hugh Darwen , David Mc Goveran are the first authors we need to read
> in order to understand our subject .

This is true to some extent, but may I suggest that there were a lot
more people who defined and shaped the database world as it exists
today.

[joel-...@home.com]

I managed to not say anything about it when Fabian posted
http://www.apa.org/journals/features/psp7761121.pdf on hjr's
clippity-clop blog, but I can no longer resist.

Someone needs to work on humor, grammar and logic!

jg
--
@home.com is bogus.

Omigod: http://dba-oracle.blogspot.com

[keyb...@yahoo.com]

Don't you know why, Mr. Pascal? A funny exchange allows everyone to
have their opinion, have their say, and not be corrected. Didn't you
know that opinions and good feelings were more important than...what's
that word people used to use..."facts"? I mean, what can you do with a
fact? You have to agree, shut up, or admit your ignorance...maybe even
do some research or something. Ew! None of that feels good, does it.

You say RM is based on mathmatics. Well, 2+2=4 is just an opinion, a
convention. Didn't you read your Orwell? *eyeroll* I used to think
the world would end up like 1984...then Huxley seemed to be onto
something. I'd never imagined we'd have a hidious blend of both, the
destruction of reasoning by the force of society - voluntarily - while
everyone laughs as if Somatized. Some in this bunch have made me think
twice.

-keyboards

[runar...@gmail.com]

"may I suggest that there were a lot
more people who defined and shaped the database world as it exists
today"

To their neverending shame.

[DA Morgan]

It is quite telling -- and very predictable -- how quickly someone
that is a purist and isn't focused on the bottom-line value to an
organization such as a business entity forgets that software is a
tool.

Do you criticize those that manufacture hammers because they
deviate from the original design?

[DA Morgan]

And you, by some self-annointing process, know more about databases
than we do? Fascinating. Likely you know more about them than the
people at Amazon.com, eBay, and all of the Fortune 500's too.

Which explains, no doubt, why you are a multi-billionaire CIO being
asked to attend conferences such as Oracle OpenWorld as a keynote
speaker.

You aren't? What a surprise!

[DA Morgan]

> ROWNUM is a hack that predated analytical SQL extensions, and some of
> these extensions do make sence for an end user. Otherwise, how do you
> express the query

Without using ROWNUM please provide the equivalent to the following:

SELECT *
FROM t
WHERE rownum < 37;

[mikharaki...@yahoo.com]

DA Morgan wrote:
> > ROWNUM is a hack that predated analytical SQL extensions, and some of
> > these extensions do make sence for an end user. Otherwise, how do you
> > express the query
>
> Without using ROWNUM please provide the equivalent to the following:
>
> SELECT *
> FROM t
> WHERE rownum < 37;

select * from (
select row_number() over (order by 0) rn
from t
) where rn < 37

As far as performance concerned, there is no reason why it can't be the
same.

Note, that unlike rownum hack, it would also provide an answer with
negated predicate

where rn >= 37

and people would stop asking why rownum doesn't return any rows.

BTW, I'm not fan of analytics, but having two constructs in the
language doing the same thing is just messy.

[DA Morgan]

Cute but I will cry foul (or perhaps fowl).

Source:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310
"It assigns a unique number to each row to which it is applied"

Source:
"ROWNUM pseudocolum returns a number indicating the order in which
Oracle selects the row from a table"

And precisely how are these substantively different? ;-)

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> > ROWNUM is a hack that predated analytical SQL extensions, and some of
> > these extensions do make sence for an end user. Otherwise, how do you
> > express the query

> Without using ROWNUM please provide the equivalent to the following:

> SELECT *
> FROM t
> WHERE rownum < 37;

At the risk of sounding stupid, what *_exactly_* does this query mean?

The first 37 rows that were inserted into the table?

The first 37 that the system happens to find in RAM or on Disk?

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[DA Morgan]

Paul wrote:
>
>
> DA Morgan <damo...@psoug.org> wrote:
>
>
>>>ROWNUM is a hack that predated analytical SQL extensions, and some of
>>>these extensions do make sence for an end user. Otherwise, how do you
>>>express the query
>
>
>
>>Without using ROWNUM please provide the equivalent to the following:
>
>
>
>>SELECT *
>>FROM t
>>WHERE rownum < 37;
>
>
>
>
> At the risk of sounding stupid, what *_exactly_* does this query mean?
>
> The first 37 rows that were inserted into the table?
>
> The first 37 that the system happens to find in RAM or on Disk?
>
>
>
> Paul...

In Oracle it means delete the first 36 rows found: Whichever ones they are.

[joel-...@home.com]

Paul wrote:

>At the risk of sounding stupid, what *_exactly_* does this query mean?

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1258

You don't sound stupid, but rownum and the top-N faq are basic
knowledge for an Oracle person. The analytic function is relatively
new, I confess I barely comprehended it before Daniel's link.

jg
--
@home.com is bogus.

http://beta.news.com.com/HP+to+ship+Netscape+browser+on+new+PCs/2100-1032_3-5887648.html?part=rss&tag=5887648&subj=news

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> In Oracle it means delete the first 36 rows found: Whichever ones they are.

So, it's kind of like an equivalent of a

SELECT Rand(MyField)
FROM MyTable
ROWS 36 (or TOP 36, FIRST 36, or whatever you're having yourself)?

[DA Morgan]

Paul wrote:
>
> DA Morgan <damo...@psoug.org> wrote:
>
>
>
>
>>In Oracle it means delete the first 36 rows found: Whichever ones they are.
>
>
>
>
> So, it's kind of like an equivalent of a
>
> SELECT Rand(MyField)
> FROM MyTable
> ROWS 36 (or TOP 36, FIRST 36, or whatever you're having yourself)?
>
>
>
> Paul...

Yes.

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> > At the risk of sounding stupid, what *_exactly_* does this query mean?
> > The first 37 rows that were inserted into the table?
> > The first 37 that the system happens to find in RAM or on Disk?

> In Oracle it means delete the first 36 rows found: Whichever ones they are.

The "delete" there is a typo, yeah?

Paul... (who just noticed it).

[steve]

On Wed, 5 Oct 2005 01:27:39 +0800, DA Morgan wrote
(in article <1128446853.42704@yasure>):

> fabian pascal wrote:
>> It's quite telling -- and very predictable -- how quickly a discussion
>> on data fundamentals is veered by people who don't know and don't care
>> about fundamentals--read: the vast majority--to a "funny" exchange on
>> hammers and wrenches. It's happened again and again online and it does
>> quite a lot to validate my claims about the state of knowledge and
>> intellect in the industry.
>>
>> It's only natural.
>
> It is quite telling -- and very predictable -- how quickly someone
> that is a purist and isn't focused on the bottom-line value to an
> organization such as a business entity forgets that software is a
> tool.
>
> Do you criticize those that manufacture hammers because they
> deviate from the original design?
>

You mean that my version .9a rock has an update?

[joel-...@home.com]

>> Do you criticize those that manufacture hammers because they
>> deviate from the original design?

>You mean that my version .9a rock has an update?

http://www.allhatnocattle.net/name_that_toon_august_2004.htm

[fabian pascal]

DA Morgan wrote:
> And you, by some self-annointing process, know more about databases
> than we do? Fascinating. Likely you know more about them than the
> people at Amazon.com, eBay, and all of the Fortune 500's too.
>
> Which explains, no doubt, why you are a multi-billionaire CIO being
> asked to attend conferences such as Oracle OpenWorld as a keynote
> speaker.
>
> You aren't? What a surprise!

If I responded to your comments the way they deserve, I would be
accused of "bad style" and somebody will probably delete it. So I won't
bother.

I will just say that what you make of urself online has been picked up
by other Oracle professionals that do know something about databases.
And it's not pretty.

As to your notion that money, corporate popularity and keynote
invitations reflect expertise and knowledge, well, it's understandable
you think that: you're american. That's the notion that brings the
country down the drain. But there's poetic justice: corporations that
employ the likes of you get what they deserve.

You're at the bottom alright: not the bottom line, though, but that of
the barrel.

[DA Morgan]

fabian pascal wrote:

> If I responded to your comments the way they deserve, I would be
> accused of "bad style" and somebody will probably delete it. So I won't
> bother.

But, of course, you did anyway.

See you at UKOUG?

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> fabian pascal wrote:

> > If I responded to your comments the way they deserve, I would be
> > accused of "bad style" and somebody will probably delete it. So I won't
> > bother.

> But, of course, you did anyway.

> See you at UKOUG?

Just don't share a room! 8-)

Paul...

[DFS]

fabian pascal wrote:
> Sorry, but you did not understand one word I said. And I am not
> surprised, because you don't have the necessary knowledge. What is
> more, there is too little intellect and reasoning ability there for me
> to bother, because I won't have any effect.
>
> I will let the readers who have the necessary capacity and knowledge
> to judge who makes sense.

Somebody's going to have to make sense, 'cause you're not.

Are you sure you're not the conceited idiot Toral who just got fired tonight
on The Apprentice? You sure sound like her.

[Turkbear]

Paul <pa...@see.my.sig.com> wrote:

>
>
>
>DA Morgan <damo...@psoug.org> wrote:
>
>> > ROWNUM is a hack that predated analytical SQL extensions, and some of
>> > these extensions do make sence for an end user. Otherwise, how do you
>> > express the query
>
>> Without using ROWNUM please provide the equivalent to the following:
>
>> SELECT *
>> FROM t
>> WHERE rownum < 37;
>
>
>
>At the risk of sounding stupid, what *_exactly_* does this query mean?
>
>The first 37 rows that were inserted into the table?
>
>The first 37 that the system happens to find in RAM or on Disk?
>
>
>
>Paul...

The first 36 records returned by the query ( undetermined as to what they will be and it is unrelated to their insertion
date )

[ravi...@gmail.com]

mikharaki...@yahoo.com wrote:

>
> > 2) Constraint Support
>
> > Eg i) a declarative constraint to ensure each invoice must have at
> > least 1 line item (which can only be simulated with a constraint on a
> > materialised view which outer joins invoice_header and
> > invoice_line_item ) .
>
> Why "simulated"? This is pretty legitimate way to enforce complex
> constraints (aka ANSI standard assertions).

The major problem with the materialized view approach is the very
fact that you have to create a materialized view! What additional
benefit does the view confer to the user? Would it not be better to
declaratively state your intent and let the DBMS figure out how to
implement it?

> Constraint enhancements is not a top priority nowadays. Wake up: the
> bleading edge of research and development is XML, Fusion, whatever.
>

Which is exactly what people have been complaining about! Database or
RM research is not about XML. They are two different fields.

> > To answer you original question I think that Fabian Pascal , Chris Date
> > , Hugh Darwen , David Mc Goveran are the first authors we need to read
> > in order to understand our subject .
>
> This is true to some extent, but may I suggest that there were a lot
> more people who defined and shaped the database world as it exists
> today.

Other than Codd I can not think of anybody else who has made
significant contributions to Relational Modelling. Of course, my
knowledge is very limited. Could you please enlighten me on the other
luminaries in this field?

Ravi

[earth...@gmail.com]

On Saturday, August 27, 2005 5:08:35 AM UTC-5, Paul wrote:
> I have been visiting the site www.dbdebunk.com recently and have found
> the musings of the author interesting. Having seen some disasters in
> my time (in terms of adherence to any sort of reasonable database
> design), I agree with a lot of what he has to say. I'm not sure that I
> completely understand his constant criticism of SQL (more reading
> required maybe?).
>
>
> He is constantly railing against vendor extensions (which, from what
> I've read, he would see as corruptions) of the Relational Model,
> however my own thougts would be that a competent professional can say
> to himself "Right, this isn't fully compliant, but it does what I want
> quickly and easily, so I'll use it until something better comes
> along".
>
>
> I would be interested in the opinions of other posters in this group,
> particulary from those who have posted stuff in the past about the
> need for the data-management industry to get itself some decent
> independent standards prevalent in other industries (medical, legal,
> architecture...).
>
>
>
> Paul...
>
>

SQL was developed independent of EF Codd's mathematical work on relational data management. It violates many relational principles by returning non-sensical data to the naive user. A proper query language based on relational principles would not allow non-sensical result sets.

[joel garry]

There is no time component of classic relational theory, so it does make some kind of sense that you would respond poorly to a nine year old thread.

I stand by my comments of 9 and 22 years ago.

I stand by my comments of I-don't-recall-which-century that Joe Celko's SQL puzzles are prima facea evidence that there are mapping difficulties between business problems and relational calculus. Part of that problem is due to the potentially large number of hidden variables in those kinds of problems, part is due to dumb analysis by users. In respect to the latter, Fabian is correct (which I've stated in this thread and elsewhere). My major complaint has always been he blames the wrong people, and therefore comes up with the wrong answers for the real world. Patton slapping the private, so to speak.

There's also some aspect of self-promotion for his way of doing things, it doesn't matter what my opinion of that is (nothing wrong with _some_ self-promotion), "the market" seems to judge it correctly. As a giant invisible face-palm.

jg
--
@home.com is bogus.

http://www.techradar.com/news/internet/cloud-services/oracle-acquires-offline-marketing-specialist-datalogix-1278232

[bas.t...@gmail.com]

On Sunday, September 25, 2005 at 11:03:48 AM UTC-4, striebs wrote:
> Paul and others ,
>
>
> Lets examine an example of Oracle extensions to SQL and commercially
> expedient solutions for problems .

>
>
> 7.3 is about 13 years old , zoom forward to 2018 - how confident are
> you that Oracle will have significantly enhanced it's constraint
> support by then ?
>
>

We are almost there! 2016 and 12C!!! Not sure we'll see a 13x by 2018..but who knows.

[joel garry]

Yes, Oracle has enhanced it's constraint support, for some strange values of "enhance." https://jonathanlewis.wordpress.com/2016/03/11/wrong-results-3/

jg
--
@home.com is bogus.

http://motherboard.vice.com/en_uk/read/oracle-america-vs-google-inc-java-API-copyright-fair-use