[Oracle] Sql request

#21

On Nov 30, 8:35*pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:

Quote:

On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney

bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
Gene Wirchenko <ge... (AT) ocis (DOT) net> writes:

On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney
bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:

joel garry <joel-ga... (AT) home (DOT) com> writes:

[snip]

Can't a function be part of the DBMS?

A non-relational function shouldn't be part of a relational DBMS, no.

This was far too broad, I now see. Originally it was in the context of a
*query* returning a non-relational result, which is really as far as I
should have taken it.

* * *I think you still have problems with the division. *(I do not
claim to be able to define the split myself. *I know how difficult it
can be to rigourously define something.)

And perhaps relvars can torture non-relations to relations. But this
is all too deep for me. I mostly agree with you guys. There should
be clear delineations between relational and non-relational parts of
systems, as well as standard SQL versus extensions and procedurals.

Quote:

* * *Addition of integral values is a function mapping two integral
values to an integral value.

Right; of course, there are heaps of functions operating on attribute
values that can be used *within* relational operations to modify the
relation that will be returned. Such functions definitely belong as part
of the relational DBMS.

What I was trying to express was that relational operations — like the
various relational operations that ‘SELECT’ implements — should only
return data as relations (they might also return status responses). They
should never return non-relation data.

* * *A relation does not have order. *This would not be a relation
* * * * * select clicode,cliname from clients order by clicode
because of the order by clause.

* * *Having to go through a sort stage when the DBMS might well have
been able to easily handle it would be counterproductive.

* * *For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

This is certainly wrong in Oracle, and ought to be wrong everywhere
else. I don't say that as an Oracle wonk, but rather because ordering
needs to be explicit by definition.

jg
--
@home.com is bogus.
http://codeoffsets.com/

#22

joel garry schreef:

Quote:

On Nov 30, 8:35 pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:
On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney

bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
Gene Wirchenko <ge... (AT) ocis (DOT) net> writes:
On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney
bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
joel garry <joel-ga... (AT) home (DOT) com> writes:
[snip]
Can't a function be part of the DBMS?
A non-relational function shouldn't be part of a relational DBMS, no.
This was far too broad, I now see. Originally it was in the context of a
*query* returning a non-relational result, which is really as far as I
should have taken it.
I think you still have problems with the division. (I do not
claim to be able to define the split myself. I know how difficult it
can be to rigourously define something.)

And perhaps relvars can torture non-relations to relations. But this
is all too deep for me. I mostly agree with you guys. There should
be clear delineations between relational and non-relational parts of
systems, as well as standard SQL versus extensions and procedurals.

Addition of integral values is a function mapping two integral
values to an integral value.
Right; of course, there are heaps of functions operating on attribute
values that can be used *within* relational operations to modify the
relation that will be returned. Such functions definitely belong as part
of the relational DBMS.
What I was trying to express was that relational operations — like the
various relational operations that ‘SELECT’ implements — should only
return data as relations (they might also return status responses). They
should never return non-relation data.
A relation does not have order. This would not be a relation
select clicode,cliname from clients order by clicode
because of the order by clause.

I thought that one was about the order of attributes, not rows.

Quote:

Having to go through a sort stage when the DBMS might well have
been able to easily handle it would be counterproductive.

For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

This is certainly wrong in Oracle, and ought to be wrong everywhere
else. I don't say that as an Oracle wonk, but rather because ordering
needs to be explicit by definition.

jg
--
@home.com is bogus.
http://codeoffsets.com/

Shakespeare

#23

Gene Wirchenko wrote on 01.12.2009 05:35:

Quote:

For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result.
Not even in Oracle 8 and and certainly not for any Oracle version > 9
(and not for any Postgres as well)

Thomas

#24

On Dec 1, 11:48*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Quote:

joel garry schreef:

On Nov 30, 8:35 pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:
On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney

bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
Gene Wirchenko <ge... (AT) ocis (DOT) net> writes:
On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney
bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
joel garry <joel-ga... (AT) home (DOT) com> writes:
[snip]
Can't a function be part of the DBMS?
A non-relational function shouldn't be part of a relational DBMS, no.
This was far too broad, I now see. Originally it was in the context of a
*query* returning a non-relational result, which is really as far as I
should have taken it.
* * *I think you still have problems with the division. *(I donot
claim to be able to define the split myself. *I know how difficult it
can be to rigourously define something.)

And perhaps relvars can torture non-relations to relations. *But this
is all too deep for me. *I mostly agree with you guys. *There should
be clear delineations between relational and non-relational parts of
systems, as well as standard SQL versus extensions and procedurals.

* * *Addition of integral values is a function mapping two integral
values to an integral value.
Right; of course, there are heaps of functions operating on attribute
values that can be used *within* relational operations to modify the
relation that will be returned. Such functions definitely belong as part
of the relational DBMS.
What I was trying to express was that relational operations — like the
various relational operations that ‘SELECT’ implements — shouldonly
return data as relations (they might also return status responses). They
should never return non-relation data.
* * *A relation does not have order. *This would not be a relation
* * * * * select clicode,cliname from clients order by clicode
because of the order by clause.

I thought that one was about the order of attributes, not rows.

Kinda. The tuples of a relation (AKA rows in a table) have no order,
which leads to no order on the attributes. Applications use queries
to access data. So we wind up with confusion between application
access and relational inquiry with SQL. It makes no sense to maintain
that SQL (of any variety) only does or should do relational
operations. It happens to be the single language of both relational
queries and applications, using the relational terminology. So Ben is
both right and wrong about user applications being the one to process
the data for users, as SQL confounds the difference. I used to (like,
15-20 years ago) think this was a big shortcoming of SQL, since it
does only part of the user application. Now that the various extended
SQL's can do all of this (even *gasp* html and xml), and the
standardization is close enough to allow it to be used as a data
access language by various tools, that is less of an issue, though the
basic differences between engines makes for a different problem. I
think we can all agree that the popular engines distance themselves
from relational theory to a great degree - but I don't think that
leads to judging these distances as bad at all. I know that is
flamebait in comp.databases, but, well, I didn't start the
crossposting.

As far as analytics, I think it is good to be able to shove some
processing into the sql engine, but I think we are also seeing that it
is hard to predict that performance will be better by doing that, as a
generality. Basically the same problem as forcing order into group
by, there are times we as programmers know better how to manipulate
the data, especially sorting and ordering mass quantities.

jg
--
@home.com is bogus.
"What does HTML stand for?"
"I don't, ah, HoT Man Links?" - heard on radio call-in trivia
contest.

#25

On Tue, 01 Dec 2009 20:48:33 +0100, Shakespeare <whatsin (AT) xs4all (DOT) nl>
wrote:

[snip]

Quote:

A relation does not have order. This would not be a relation
select clicode,cliname from clients order by clicode
because of the order by clause.

I thought that one was about the order of attributes, not rows.

It is both. Remember that a relation is a set of tuples. A set
has no order.

[snip]

Sincerely,

Gene Wirchenko

#26

On Tue, 01 Dec 2009 23:44:58 +0100, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

Quote:

Gene Wirchenko wrote on 01.12.2009 05:35:
For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result.
Not even in Oracle 8 and and certainly not for any Oracle version > 9
(and not for any Postgres as well)

It does in my dialect (Visual FoxPro). If you are correct, I
overgeneralised.

Granted that that would be implementation-dependent, but it would
be convenient for it to do it that way while grouping. (How else
would it?) My point is that if the DBMS does any sorting internally,
the relational-data-to-app converter should get the benefit of it if
it needs it. It would be silly for the DBMS to sort internally and
then the converter do it again.

Sincerely,

Gene Wirchenko

#27

Thomas Kellerer schreef:

Quote:

Gene Wirchenko wrote on 01.12.2009 05:35:
For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result. Not even in
Oracle 8 and and certainly not for any Oracle version > 9 (and not for
any Postgres as well)

Thomas

Never? It did at least in Oracle 7. As a well known and too many times
used side effect. I remember having to reprogram queries because
developed programs relied on the sort....

Shakespeare

#28

Gene Wirchenko, 02.12.2009 04:10:

Quote:

Granted that that would be implementation-dependent, but it would
be convenient for it to do it that way while grouping. (How else
would it?)
At least Oracle and Postgres do some kind of hashing most of the time, which does not sort the results at all.

Quote:

It would be silly for the DBMS to sort internally and
then the converter do it again.
Agreed. But I think any modern optimizer will combine these steps if that would be quicker

Regards
Thomas

#29

Shakespeare, 02.12.2009 08:13:

Quote:

Thomas Kellerer schreef:
Gene Wirchenko wrote on 01.12.2009 05:35:
For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result. Not even in
Oracle 8 and and certainly not for any Oracle version > 9 (and not for
any Postgres as well)

Thomas

Never? It did at least in Oracle 7. As a well known and too many times
used side effect. I remember having to reprogram queries because
developed programs relied on the sort....

I don't know about Oracle 7, but check out these articles by Tom Kyte:

http://asktom.oracle.com/pls/asktom/...74320098178823
http://tkyte.blogspot.com/2005/08/order-in-court.html

Thomas

#30

On Dec 2, 2:13*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Quote:

Thomas Kellerer schreef:

Gene Wirchenko wrote on 01.12.2009 05:35:
* * *For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result. Not even in
Oracle 8 and and certainly not for any Oracle version > 9 (and not for
any Postgres as well)

Thomas

Never? It did at least in Oracle 7. As a well known and too many times
used side effect. I remember having to reprogram queries because
developed programs relied on the sort....

Shakespeare

Oracle never sorted the result set, it sorted the table data to effect
grouping. That the result set retained the ordering enforced to
provide a group by result was, and is, a side effect of the
operation. In that regard Oracle still sorts (orders) the groupings,
they're simply in hash key order rather than column order. Still,
it's not the result set being ordered, it's the interim processing
doing the ordering. And, as stated earlier, it was merely a happy
coincidence that the one affected the other.

David Fitzjarrell