I found out recently that when using Object-Relational Mapping tools (ORMs) like Hibernate and JDO, results are paginated in the application space when doing a join. IOW, if I do a select * from A, B where A.id = B.A_id (effectively anyway - I would actually use the tool’s query language), all rows found are returned to the requesting application and then truncated to get the desired subset (e.g. rows 26 - 50). The way around this apparently is to create a view based on the desired query, but if you want to paginate an arbitrary query, you have to get all the rows first. Why is that? What is it about databases that makes it hard to paginate the results? Why is the method for doing this not part of standard SQL?
Thanks for your help,
Rob
The ISO SQL standard defines a feature called “scrollable cursors” which allows an application to jump to the Nth row of a result set (the feature allows more than this, but I’m only describing what you’ve asked about). Not all relational database systems support scrollable cursors - they can be very hard to implement, especially when combined with other common features (such as isolation levels). Generalized applications such as Hibernate usually avoid using features that aren’t in all the database systems they’re expected to work with.
As for what makes it hard - in the general case, a DBMS has no way of telling in advance which will be the Nth row of a query result. Also, in the general case, it would be almost impossible to figure out what the internal state of the query would be at the Nth row (e.g. with a join it would have to figure out where to position the scan of each table involved).
As a result, in most cases if you ask the DBMS to scroll to the Nth row, it will have to go through rows 1 through N, even if it only returns the Nth row to the application. Since scrollable cursors allow one to scroll backwards as well as forwards, the DBMS may store the query result in a temporary table so that it won’t have to start over from the beginning if you ask it to go backwards.