Recommeded way to delivers million rows to a client

[Anne Forumer]

This is a curiosity question but, I'm sure, has been implemented many times.

If a query from a web client results in very large number (million) of rows of data, what are some of the preferred ways to deliver them to the client? The client may decide to see none to all of the data? Should the data be delivered to client in pages in a piecemeal fashion or all at once? How should the database access be configured for such a scenario?

Thanks

Anne

[David Harkness]

The first step is determining which queries might result in large result sets. The second step is to alter those queries so they don't and instead use pagination directly in the query. The reason is that loading that much data to the application server is going to kill the application just as sending a million rows to the client will.

There are specific tricks for doing pagination in the database, but if you application has enough users you may want to use more intelligent tricks. The simple one does searching, sorting, and pagination in a single nested query with two inner queries.

Briefly, the innermost query performs the search and sort. The query wrapping that adds the rownum column and cuts off the upper bound. The outermost query cuts off the lower bound.

Here's an example that returns page P of all science fiction books, each page being 25 books.

select title, author, isbn
from (select title, author, isbn, rownum rn
      from (select title, author, isbn
            from books
            where genre = 'SciFi'
            order by title, author)
      where rownum < P * 25 + 1)
where rn >= (P - 1) * 25 + 1 ;

Note the need to alias rownum to use it in the outermost where clause. You can find a good discussion of this technique on Oracle's Ask Tom site.