Hi,

I have an query which is done for pagination based on rownum

select * from ( select row_.*, rownum rownum_ from (.. my query ..) ASC) row_ where rownum <= 30) where rownum_ > 20

Actually this pagination query is generated by hibernate ORM java tool.

There is an issue we are getting some records are repeating in multiple pages.
to fix this issue we added order by rowid to my query. But this is hitting performance by 2 secs in 6 lacs records in table.

can any suggestions to fix the pagination issue with out performance hit.

Regards,
Charan

ROWID is an odd thing to order by. If the displayed order is of no importance, try ordering by an indexed column such as the primary key of the main table. This might avoid a SORT operation.

I was already sorting on column (which is not unique values).

if i do with primary keys it is also hitting performance, than the row id.

thats why i used row id.

OK, fair enough. Well your issue is really one of tuning the underlying query, not with the pagination as such. Have you used autotrace to see what it is doing?

Since you are displaying a pagination result set, you are more interested in getting some rows back early rather than getting the whole result set quickly, so you could try adding a FIRST_ROWS hint.

Thanks for you reply

But i can't do in my application and test with hints we write HQL queries which does not support the hints of oracle can you suggest me any alternative solution as such doing with normal quering.