• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

oracle pagination query for bulky data

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Post the query you already have -- the one without the sort. Adding the order by there is easy.
 
Dharmakumar Gajendran
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot for your valuable suggestions.

 
reply
    Bookmark Topic Watch Topic
  • New Topic