Paging very large result sets.

Posted on 2005-04-26
Medium Priority
Last Modified: 2011-08-18
Hey experts!

I need to page extremely large result sets through to a java client.  if my select statement returns say millions of rows, how can i get teh first set back to the gui without blowing up the client with out of memory errors...

TOAD seems to do this by pagin the results on the server - maybe using snapshots?  can i access the snapshot directly (in 9.2) or is there some other magic i should explore?

thanks for pointers.
Question by:randyd
LVL 25

Expert Comment

ID: 13869871
Normally what you do (and what TOAD is doing) is open a cursor and then fetch a certain number of rows--and display them.  TOAD keeps the CURSOR open, and if you page through the results, it fetches more rows and adds them to the display.  Depending on your program, you may be able to do that as well.
LVL 22

Expert Comment

by:earth man2
ID: 13871386
By default Oracle JDBC has a FetchSize of TEN rows.  That should not break your java program.

void setFetchSize(int rows) throws SQLException is available in all Statement, PreparedStatement, CallableStatement, and ResultSet

Author Comment

ID: 13872627
The Ten at a time is not a problem... i just cant expect the client to have enough ram to hold the entire set at once.  but i still want to be able to scroll around the result set in a list for instance...

there is a cursor that lets you move to PREVIOUS as well as NEXT values.  this is what i need to do i think.  I know this is available in OCI calls and I believe it is available in JDBC2.0 ...   to make my GUI work (nicely) i think i need to know the number of records the cursor contains... but i dont think i will until i page through to the end...  

any comments on an approach here?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 48

Accepted Solution

schwertner earned 672 total points
ID: 13873624
This seems to be a design error.
"if my select statement returns say millions of rows".
This is bad design. By no means your select statement should return
"if my select statement returns say millions of rows".

This is not possible for frontend application - the only reader of these rows
is a human being. And no numan being can observe, read and accept milions of rows.

Also the load on the server, application server and net channel is to high.

So speak with the designer and project lead and sort out why you have
to select milions of rows.

Assisted Solution

grim_toaster earned 664 total points
ID: 13873955
You will get the out of memory errors if you attempt to cache all of the results!  You will at some level even if you use scrollable cursors with Oracle.  See http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm In particular read the section "Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer."

As to the solution, I would recommend looking into something like Hibernate that has quite a good ability at handling paging using a technique similar to below.

Anyway, to actually implement paging efficiently in Oracle, it would be preferable to re-call the query each new page the client views (as schwertner pointed out users rarely ever go past the first page if there are too many results, but sometimes they do).  So, in relation to that, here's how to wrap you query using pagination, but there is one slight problem, you cannot guarantee that no-one will change the underlying data between calls and possibly miss entries with each call.

Required parameters: page & no_per_page, obviously should have some constraints set to ensure page >= 1 and no_per_page >= 1.

SELECT t2.object_name
FROM   (
        SELECT t1.object_name,
               rownum AS row_num
        FROM   (
                -- This is where your actual query will go...
                SELECT object_name
                FROM   user_objects
                ORDER by object_name
               ) t1
        WHERE  rownum <= (:page * :no_per_page)
       ) t2
WHERE  row_num > ((:page - 1) * :no_per_page);

Assisted Solution

plamen73 earned 664 total points
ID: 13874115
Just to add to grim_toaster answer and your assumption:
....to make my GUI work (nicely) i think i need to know the number of records the cursor contains... but i dont think i will until i page through to the end...  

yes, it is absolutely sure that you can not retrieve the number of all records, without actually read them, which compromises the paging.
So, you have two approaches:
1. Either forget about total number of records (pages respectively) and provide "Previous" and "Next" buttons only, or
2. Get the total number of pages and make it very slowly, especially the first time, when you have to execute
select count(1) from  ... at least

I prefer approach 1, but very often some Product Managers promises the 2nd to the customers.

something about:

>>>but there is one slight problem, you cannot guarantee that no-one will change the underlying data between calls and possibly miss entries with each call.
Actually the "paging" query avoids this problem.
Imagine you are at the 2nd page. Somebody inserts a record which gois in the 1st page. when you click previous, you will start counting from the 1st record and will get exactly the first no_per_page records, including the new one....and so on.


Expert Comment

ID: 13874254
Two points to that...

--> Get the total number of pages and make it very slowly, especially the first time...
Not necessarily, you can do as Google and many other paging operations do, and only see if there are at least X number of pages left, for example:

SELECT CEIL(count(*) / :no_per_page) AS remaining_pages, count(*) FROM (
SELECT rownum AS row_num
FROM   all_objects
WHERE  rownum <= (:page * :no_per_page) + (:no_per_page * :max_no_pages)
) WHERE row_num > (:page * :no_per_page)

Seeing as you will only be able to show the user that there are X number of available pages, it makes sense to only see if there are those X number of pages left.  And then you can stretch the available pages with each different page viewed.

-->Actually the "paging" query avoids this problem.
I was thinking more of if you were viewing page 2, someone deleted an entry on page 1, you then go to page 3 and you will not see what would have otherwise been the first entry on page 3.  To be honest, no-one would really notice, but it's just one of the things that needs to be considered.

Author Comment

ID: 13876085
hey thanks guys for the lively discussion :)

schwertner - i realize that it is not practical to return mega-result sets - not usable - not digestable etc...  but unfortunately, I, like TOAD and other apps, am not able (or willing) to restrict the SQL that the user may attempt to execute on the system.  I must beleive that they will say select * from somewhere that has too many rows - whether people do this becuase they are lazy and dont want to think about restricting with a where clause - or dont know the DESC command so they want to see columns, or they simply dont know that there will be a million rows in that pile..  not sure.  but in the end it doesn't amtter - they will issue that query and i need to handle it gracefully... (i don't call that a design error)

grim_toaster - exactly! the out of memory is a killer - and super-tricky (impossible?) to predict it is about to happen - so can't be avoided - and as an added benefit, app behavior stinks exceptions aren't thrown well etc.  

plamen73 - i thought about the select count(*) from () method - if that runs in a background thread while the first rows are being returned then the GUI might be ok...

I was thinking that read-consistent view was important - i might have to give that up in favor of making this thing work...  you might guess - but my other wrinkle is i do not know the SQL statements ahead of time and i don't want to get into parsing random SQL and inserting the paging stuff in the middle...

thanks so far... still noodling..

Expert Comment

ID: 13876613
As a question for you, how is the SQL provided to you?  

If you are actually passed a select statement to execute, where are you executing it?  In a stored procedure via dbms_sql of nds or on the java side via a prepared statement?  Although I must admit I don't like the sounds of it, so many security holes etc.  But if it is a requirement...

Anyway, if I can attempt to persuade you to use hibernate (which has paging support already provided) to send your custom SQL to would be the simplest way, or if you can download the code and look at the Oracle9Dialect class to give you some ideas as to how to do this.

But in a nutshell, the only way to do what you are after (from what I understand) is to wrap whatever query they provide with the pagination code, thankfully it can fully wrap the SQL passed (see above example) and just ignore the extra row_num column that would be provided.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month16 days, 1 hour left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question