|
|
Posted by --CELKO-- on 03/08/06 03:08
>> Clearly, at some point in the process users will need to see data in a particular order. <<
My first response was "Which particular order? Why?", but then I
realized that this is not true. I can design a parallel language which
uses no ordering in the outputs.
>> Is there is a chance in hell of getting such an element added to the definition of a view? If so, where do I sign up for the campaign? <<
You can sign up for the ANSI Standards committee and write a proposal.
But you will need to defend it. And since we have looked at this issue
before, you will have to work at the level of a PhD to get around the
objections and proofs of contradiction that other people found in it.
Let's throw out the foundations of the Relational Model and assume
that we can write crap like
SELECT ..
FROM ..
WHERE ..
[GROUP BY ..]
[HAVING BY ..]
[ORDER BY ..];
If I have ordering on VIEWs, I must have ordering on TABLEs. Or you
have to change the RM to have both ordered and unordered tables with
all the rules for moving data between them.
A. What is the meaning of sorts by things not in the select clause?
Gee, I guess we need to carry extra data on each row for the sort, even
if it is not used.
SELECT a, b
FROM Foobar
ORDER BY x;
Even worse, what is the output of this? Remember that a GROUP BY
destroys the original table to produce a grouped table, so x does not
even exist anymore.
SELECT a, SUM(b)
FROM Foobar
GROUP BY a
ORDER BY x;
B. What is the meaning of sorts on nested subqueries? Which sort has
priority?
SELECT a, c
FROM (SELECT x, y
FROM Barfoo
ORDER BY x) AS XX(a, c)
ORDER BY c, a;
C. What is the meaning of sorts on UNION, INTERSECT and EXCEPT?
Remember that (A UNION B) = (B UNION A), or do you want to throw out
all of Set Theory along with the Relational Model?
SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY x;
What if I add an ORDER BY to the whole thing?
SELECT r, s
FROM (SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY y)
AS XX(r, s)
ORDER BY r, s;
D. What do UPDATE, INSERT and DELETE with ordered table mean? For
example:
INSERT INTO Foobar -- asc ordering
SELECT .. FROM Barfoo; -- desc ordering
E. Another problem here is that there are two kinds of sorts, stable
and non-stable. Now the Standard has to pick an implementation method.
We hate doing that.
Non-stable sorts are much faster than stable (aka "sequence
preserving"); look up QuickSort versus Bubble sort. With the current
cursor model, I have one and only one sort which can be done any way
the optimizer thinks will work best.
If you want non-stable sorting, then only the last sort applied to the
result table matters. But which one is it?? Well, now we need to
specify the absolute order of execution - no optimizer changes,
please.
I also cannot parallelize my code because it would not have an
ordering.
Why is this ordering worth destroying any possible way of having
parallelism or optimization? One of the best things about a VIEW is
that the optimizer can "cut & paste" the VIEW into a query and
fetch the rows in whatever order is required. Your model has to
materialize and then order the table, even if it screws up
optimization.
The advantage of a single ORDER BY in the cursor is that it can be
factored into the optimizer by looking at indexes, or saved for the
middle tier.
[Back to original message]
|