This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes oracle pagination query for bulky data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "oracle pagination query for bulky data" Watch "oracle pagination query for bulky data" New topic
Author

oracle pagination query for bulky data

Dharmakumar Gajendran
Greenhorn

Joined: Feb 22, 2010
Posts: 20

Hi,

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.

Thanks & Regards,
Dharmakumar G
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
Dharmakumar Gajendran
Greenhorn

Joined: Feb 22, 2010
Posts: 20
Martin, you are right. We do have option for the filter. So the user can depend on the filters.

Thanks for your reply.
Dharmakumar Gajendran
Greenhorn

Joined: Feb 22, 2010
Posts: 20
Hi

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.

Can any give the sql query format.

Thanks.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Post the query you already have -- the one without the sort. Adding the order by there is easy.
Dharmakumar Gajendran
Greenhorn

Joined: Feb 22, 2010
Posts: 20
Here is the query


-------------------------------------------------------------------------------------------

select *
from ( select /*+ FIRST_ROWS(25) */
a.*, ROWNUM rnum
from ( /*** my query **/
ORDER BY 1 desc
) a
where ROWNUM <=25)
where rnum >= 1

-----------------------------------------------------------------------------------------------------------------

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.

Thanks .
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
Dharmakumar Gajendran
Greenhorn

Joined: Feb 22, 2010
Posts: 20
Thanks a lot for your valuable suggestions.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: oracle pagination query for bulky data
 
Similar Threads
Pagination
PAGINATION
Pagination
Pagination in struts2
Pagination