ADQL - problems with LIMIT OFFSET on sharded DBs

[Kristin Riebe]

Dear DAL,

I just realized (well, actually one of our database users pointed me to
it), that OFFSET causes some trouble for sharded database systems, where
the data is distributed to a number of shard nodes (e.g. spider engine
on MySQL). This does not mean that it shouldn't be included in the next
ADQL version, I just want to caution that data providers using
distributed databases may not want to support it.

For example, if there is a query using LIMIT 100 or TOP 100, we just
hand this query to the shard nodes, collect 100 result rows from each in
one temporary table on the head node and then apply a LIMIT 100 again,
to just return the desired 100 rows (obviously this makes only really
sense, if an ORDER BY clause was applied as well).

For OFFSET, we cannot do it this way, because it is not clear where the
OFFSET on each shard node has to start. If we simply apply the same
behaviour as before, e.g. with LIMIT 100 OFFSET 2000, this would mean we
collect 100 rows from each of the shards in a combined temporary table
on the head node. For an example of 10 shards, we have now 1000 rows on
the head node. Applying LIMIT 100 OFFSET 2000 again (as with the simple
LIMIT before) will now return nothing.

In order to handle it properly, we would need to pretend that the data
was in only one table. So we should not apply LIMIT OFFSET to the shard
nodes, but instead collect *all* data on the head node and then apply
LIMIT OFFSET on this combined table. Which can be a huge overhead.

For other distributed systems where the nodes can communicate with each
other, there may be better solutions, but for our system OFFSET won't
work. Queries would either not return results as expected from the user
or run into timeout.

So we either won't support it, tell users that it will return different
results than on a single-server-database or we discourage users from
using it and if they do it anyway, they most of the times will get a
timeout.

Cheers,

Kristin



On 04/12/2016 09:27 PM, Walter Landry wrote:
> Patrick Dowler <pdowler.cadc at gmail.com> wrote:
>> As far as I can see, Sybase ASE (15.x) does not support LIMIT or
>> OFFSET.  I don't see anything about Oracle above and that is used by
>> ALMA and could be adopted by other projects for "reasons".
> 
> We (IRSA) also use Oracle.  Oracle added OFFSET in version 12, but we
> are stuck on 10.2.  The workarounds I have seen are pretty ugly and
> inefficient [1].  It requires rewriting the query to add a row number.
> Then we filter based on that row number.  Finally, we would have to
> remove the row number (but only if the user did not ask for it!).  So
> there is a fair amount of query analysis that I do not want to think
> about.  That makes it sufficiently annoying to implement such that
> we would probably upgrade to a newer version of Oracle first.
> 
> Cheers,
> Walter Landry
> 
> [1] http://stackoverflow.com/questions/7480243/sql-oracle-order-by-and-limit
> 

-- 
-------------------------------------------------------
Dr. Kristin Riebe
E-Science & GAVO

Email: kriebe at aip.de
Phone: +49 331 7499-377
Room:  B6/25
-------------------------------------------------------
Leibniz-Institut für Astrophysik Potsdam (AIP)
An der Sternwarte 16, D-14482 Potsdam
Vorstand: Prof. Dr. Matthias Steinmetz, Matthias Winker
Stiftung bürgerlichen Rechts
Stiftungsverzeichnis Brandenburg: 26 742-00/7026
-------------------------------------------------------