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.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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?
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.
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.oracl
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);
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.
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.
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..
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.
Business Accounts
Answer for Membership
by: jrb1Posted on 2005-04-26 at 12:14:52ID: 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.