For example: Being able to start displaying or manipulating rows as they are being returned by a query while the query has not finished searching the table?.. and I don't mean using the FIRST ROWS directive to hint the engine, I mean transparently, without having to add addtional logic. A feature like this would be useful for queries which take a long time to complete.
+2
A:
SQL is a relational algebra meant to operate on sets (not subsets) and, in my opinion, would not gain anything if modified to allow this.
If you want this capability, it's easy enough to make two round trips to the database, the first with fetch first 100 rows only and display it quickly, the second without, to retrieve the entire set.
There's nothing stopping an implementation (such as DB2) from returning the first N rows quickly while it still transmitting the rest down.
paxdiablo
2010-06-20 02:03:55
@paxdiablo - *"There's nothing stopping..."* The nature of the SQL might prevent this; e.g. presence of `ORDERED BY` when that ordering requires the DB engine to sort the resultset.
Stephen C
2010-06-20 02:31:58
I was thinking more along the lines of the DB driver (like a JDBC client) delivering the first part of a set to the application while the set is still being sent over the wire. This would be _after_ the DBMS has fully formed (and ordered) the set. Obviously you can't start delivering data until it's sorted.
paxdiablo
2010-06-20 04:40:22
@paxdiablo- I'm talking about starting to display/manipulate rows returned by a query into the current list before the query has completed its execution, whether it be a full table scan or completed its sorting, like a look-ahead buffer.
Frank Computer
2010-06-20 05:34:46
@Frank, you _cannot_ return an ordered set until it's ordered and it cannot be ordered until you have all the data. If the set is not ordered or if you're using an index that guarantees delivery in order (such as using an index on column X with a query containing `order by X`), it might be feasible. But very few queries in the wild are unsorted.
paxdiablo
2010-06-20 07:29:59
A:
Do you know for a fact that the big DBs like Oracle don't already do this? You do some code that is a select * from blah and start getting results streamed to your client, do you know for a fact Oracle has the query 100% figured and loaded in memory out before it starts sending the first byte?
(I don't know either, but it would not surprise me if it were true, at least in certain cases)
bwawok
2010-06-20 02:06:22
+2
A:
If you think about it, that would only work for queries that do not have and ORDER BY or GROUP BY directive. It seems like what you are asking is something like the way a "dump" works, where it starts writing to a file before it's done reading the table. You could start reading the file, and processing data, before it done dumping.
Some databases support buffered and unbuffered query processing, which is partially what you are looking for.
Brent Baisley
2010-06-20 03:20:29