Performance Problem with Order by Clause in DB2

[gecashish]

If i use below query
Select * from ADJ_ADJSTMNT fetch first 21 rows only with ur;
works fine
But if i use the same query with order by clause like below
Select * from ADJ_ADJSTMNT
order by adj_ca_id
fetch first 21 rows only with ur;

It's very much time consumming.
Pls note that ADJ_ca_id has an primary index,It's must for me to select all records from ADJ rather then some specific fields from ADJ

What could be the possible solutions.

Many Thanks in Advance

[wellhole]

I'm not completely familiar with db2, but my question is whether that column is the only key in the index. It might help if this is how its set up.

One way to work around it is to keep track of what records you're interested in. Perhaps another file that is modified whenever table is updated and you'll query on that instead.

[cburnett]

You might want to also include the clause OPTIMIZE FOR 21 ROWS at the end of the statement as well; this tells the optimizer that you only expect to to retrieve 21 rows (Yes I know that the FETCH 21 ROWS ONLY clause infers this, but worth a try anyway).