Classic problem of Paging through Records

[rajaha]

I am trying to solve the classic problem of paging with Oracle Query. I am writing an ASP.NET application that given the 'Start' record number and 'PageSize', must return the records in certain format.

Is there a generic solution for solving this problem in Oracle? I am basically using the following query:

Select t.Key, t.Title, t.DateCreated, t.Description, ...
From Items t, (
Select Key, RowNum rNum
From (
Select Key
From Items
Order By DateCreated
)
Where RowNum <=
) selected
Where selected.Key = t.Key And rNum >

This solution works, but does not scale at all. The performance is horrible as 'Start' gets bigger and bigger. I am talking about hundreds or thousands or records in the DB .

Any scalable solution to this classic problem would be great. Please help. If there is a better forum to post this, please do let me know.

Thanks in advance.

Regards,
Raja.

[DaPi]

Here's Tom Kyte on the problem:
http://asktom.oracle.com/pls/ask/f?p...IA:76812348057

[rajaha]

I am already doing what the article suggests - pretty similar if not exact. However, this solution is only to target human end-users who is willing to go only so far as a few pages of data with only options of accessing 'previous page' and 'next page' at a time.

What I am trying to write is an ASP.NET app that is willing to take any paging parameters. If asked for a page starting from millionth record, it should just serve it. Unfortunately the solution in this article is great up to a hundred or two of pages and just doesnot scale beyond that.

Anyway, as Oracle does not support query language to do this efficiently, I am looking into C# and .NET solutions with efficient caching of stream readers and so on.

Thanks for the reply.

Raja.

[DaPi]

Originally posted by rajaha
If asked for a page starting from millionth record, it should just serve it.

This implies some kind of order - in general this will require sorting the whole result set. If your sort is on an index then that could be faster. Did you look at adapting Tom's "hits table"?

In your case since you are retrieving records by DateCreated, couldn't you add the "page number" as an indexed column?

[tamilselvan]

===
In your case since you are retrieving records by DateCreated, couldn't you add the "page number" as an indexed column?
===

I welcome "page number" idea.

Tamil