Paging enormous tables on DB2

Question

We have a view that, without constraints, will return 90 million rows and a reporting application that needs to display paged datasets of that view.

We're using nhibernate and recently noticed that its paging mechanism looks like this:

select * from (select rownumber() over() as rownum, 
    this_.COL1 as COL1_20_0_, 
    this_.COL2 as COL2_20_0_ 
    FROM SomeSchema.SomeView this_ 
    WHERE this_.COL1  = 'SomeValue') as tempresult 
where rownum between 10 and 20

The query brings the db server to its knees. I think what's happening is that the nested query is assigning a row number to every row satisfied by the where clause before selecting the subset (rows 10 - 20). Since the nested query will return a lot of rows, the mechanism is not very efficient. I've seen lots of tips and tricks for doing this efficiently on other SQL platforms but I'm struggling to find a DB2 solution. In fact an article on IBM's own site recommends the approach that nhibernate has taken.

Is there a better way?

Answer 1

Unfortunately from what I know it is the only approach. However I think that first you need to identify the time spent on the query components - use visual explain or the snapshot/event monitoring tools.