I will be grateful if any one could help me out to sort this issue.
I am using rownum for pagination in oracle sql. I am fetching 25 records in each request. There are 50000 records. for first 5000 records that is records between 4976 and 5000 the time consumption to fetch the data is fine. But
when i am trying to fetch beyond 20000 records that is records between 19976 and 20000 the time consumption is huge (taking around 5 mts) which is not feasible in the application. can any one has the solution to fix this please post your comments.
With page size of 25 the 5000th record is on page 200, 20000th record on page 800. Are your users really going to browse hundreds of pages? That does not sound like a good solution. You should implement filters so that your users can find record they look for on first few pages, not on pages in the order of hundreds. Another reason is there is simply no way to make pagination query fast for that many pages, in any database.
If you want to see how other software copes with task like this, try accessing page 200 in a search result of some large Google search.
Joined: Feb 22, 2010
Martin, you are right. We do have option for the filter. So the user can depend on the filters.
Thanks for your reply.
Joined: Feb 22, 2010
Now I have one more challenge. I need to sort the column for the given data. the requirement is to sort(asc or desc) for the selected column and then select the 25 records.
Dharmakumar Gajendran wrote:What's happening here is for ORDER BY 1 the query returns the result fast. But while sorting other than 1st column its taking more time.
So you know how to sort. I'd just recommend creating a view returning the base data and then using the pagination query just with the view. It would simplify the things out.
Sorting by first column is fast most probably because there is an index on that column (I guess the ID is a primary key, and therefore it is indexed). You need to add an index on other columns you want to sort by. If you'll run this query with several different orderings, you'll need to create an index for every ordering you plan to use. If you want to sort by expression, you might use function-based index.
Additionally, I'd say that the /*+ FIRST_ROWS(25) */ hint should appear in the outermost select, not in the inner one. But if your query is fast enough (when sorting by orders.id, that is), the query plan is certainly good and moving the hint should not change anything.
And finally, calling PL/SQL functions from an SQL is a bit slower than pure SQL. I don't know what your functions do, but if they could be converted to pure SQL, you might get better response times. In either case (SQL or PL/SQL - assuming the functions are deterministic) a materialized view might help too, but we're getting rather far from the original topic now.