Skip to Main Content
  • Questions

  • EclipseLink and JDBC Paging in huge Resultsets

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Antonis.

Asked: November 29, 2010 - 1:43 pm UTC

Last updated: February 09, 2022 - 2:44 pm UTC

Version: 11

Viewed 1000+ times

You Asked

Hello Tom.
Checking you answers i did not find a good solution for my problem.
To be honest with you, i am using 99% MySQL 5 for my Projects.
This time i am forced to use ORACLE 11g Database , and i am looking for an answer for the following problem:
My Database is large enough (20.000.000 records in my main transaction table) with many references to other tables (relationships)
I am using JPA 2 and JDBC for my Java Project.
I would like to create an efficient paging for my Browsers. I understand that there is no "LIMIT" clause (ok its not MySQL).
Well what do you think is the most efficient way to "load" only pages of my transaction tables?

and Tom said...

... This time i am forced to use ORACLE 11g Database ..

you really know how to butter someone up don't you.

If you used Oracle first and then mysql, you would be asking a mysql developer "I understand there is no rownum or row_number() feature in mysql (ok, its not oracle), what would...."


https://asktom.oracle.com/Misc/oramag/on-rownum-and-limiting-results.html
https://asktom.oracle.com/Misc/oramag/on-top-n-and-pagination-queries.html

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Walter, December 16, 2010 - 2:54 pm UTC

>My Database is large enough (20.000.000 records in my main transaction table) with many references to other tables (relationships)

For 20 Mill you say its large enough. LOL!

We process about 10 million records in just one day for one our table. Don't worry give yourself some time and you will see the difference :-)
Tom Kyte
December 16, 2010 - 9:20 pm UTC

Yeah, it is funny what "large" is to various people.

The problem I have is that a number like 20,000,000 is meaningless

Is that 20mb or 20tb or something else?
Do you select 1 record, 100 records, 1,000,000 records from that at a time?
Do the number of records you select grow over time as the table does or stay the same?
And so on and so on.

20,000,000 is not really informational, it leads only to other questions.

That and I start at 1,000,000 for many demos on my laptop. When showing something big - we go for billions of wide records (about .75 tb - not on my laptop ;) )

Link to answer doesn't work

Paul Kilpatrick, February 08, 2022 - 7:34 pm UTC


Chris Saxon
February 09, 2022 - 2:44 pm UTC

Thanks, I've added updated links
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.