Introducing Pagination to a DB2 query

db2udbgirl · #1 (**permalink**) 03-03-09, 12:30

Env: DB2 V8.2/AIX 5.3

I would like to add pagination feature to the following query

Quote:

select
t1.participantid, t3.queuelocation, t3.createdate,
t1.dlgt_id, t3.duedate, t1.task_type_id, t1.task_resolution_code,
t3.enddate
from
tab1 t1
left outer join
tab2 t2
on
t1.id = t2.id,
tab3 t3,
tab4 t4
where
t1.id=t3.id and
t4.case_id=t3.caseid and
t1.participantid = '?'
order by
t1.code, t1.task_num
fetch first 50 rows only

Modified Query

Quote:

with temp as
(
select
row_number() over (order by t1.code, t1.task_num) as row_id ,
t1.participantid, t3.queuelocation, t3.createdate,
t1.dlgt_id, t3.duedate, t1.task_type_id, t1.task_resolution_code,
t3.enddate
from
tab1 t1
left outer join
tab2 t2
on
t1.id = t2.id,
tab3 t3,
tab4 t4
where
t1.id=t3.id and
t4.case_id=t3.caseid and
t1.participantid = '?'
)
select
*
from
temp
where
row_id between 50 and 100

Is this a right approach or do I need to make any change from performance perspective ? Thanks

ARWinner · #2 (**permalink**) 03-03-09, 13:27

It is OK, but every time you want another page, you rerun the whole query. Why not create a temp table, insert into from your outer query with the row_number() function, put an index on the row number column, then do you page queries on the temp table?

Andy

db2udbgirl · #3 (**permalink**) 03-03-09, 13:36

Thanks for the nice idea. I will consider it.

Only problem is that my query's order by clause would vary based on user's selection from the front end portal. Users can select order by based on 5 categories. So whenever users select different category I think I need to delete the record in temp table and reinsert again.

I will do some test to determine which is a better option in that case.